Search results

Dear Syedali/Luke, the search formula worked ! thanks a lot. Warm Regards, Shibulal

=IF(COUNTIF($A$4:$A$15,"*"&$D$7&"*")<COLUMNS($E7:E7),"",INDEX($B$4:$B$15,SMALL(IF($A$4:$A$15="*"&$D$7&"*",ROW($A$4:$A$15)-MIN(ROW($A$4:$A$15))+1),COLUMN(A1)))) Why do this formula giving #NUM!

Thanks Narayan, Thanks Somendra. will catch up soon .....

no it is XLSM since I have a small VBA code to close the file......

@Somendra Misra at present it is 70284. this will increase on an average of 500 rows per day.

@ somendra, for testing purpose, I can say yes. When I work with a genuine report file, I will have different records in each cell.

@ Narayan, Have you filled the data continuously (without any blank row in between) from A1 to A1040000 ?.

Dear Somendra, I checked it with other machines having different memories. (a lil' variations). All the machines has given same result. From my curiocity, I have filled first 5 rows (A1 to D5) and then filled last 5 rows (A100000 to D100000). Here the index formula worked.Extracted the values...

@Somendra Misra https://support.office.com/en-za/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f Check "Cross Worksheet Dependency".

Is it because of cross worksheet reference of array formula which limits to 64000 ?. If so, is there any solution to extract all the values beyond row 64000 ?

Dear Small(BIG)man, Where can I see the dash board ?.

I have a sheet which extract the desired result from an another sheet with a formula like : {IF(COUNTIF(Ledger!B:B,Chk!$M$9)<ROWS($K$13:K13),"",INDEX(Ledger!F:F,SMALL(IF(Ledger!$B$2:$B$70284=Chk!$M$9,ROW(Ledger!$B$2:$B$70284)),ROW(Ledger!B1))))}. But it is not fetching the value beyond rows...

Hi Faseeh, I failed to understand from the link. And I fear whether I could explain my request clearly. It is somewhat like a conditional formatting based on formula. =If (projected value is &#60;=actual value "line chart color is green),(projected projected value &#62;= Actual value,"line...

I have a line chart which shows the usage against the projected value. Projected value is in green color and the exact value in black. Now, I need the color to be changed automatically from black to red when the exact value exceeds the projected value.

jmackeyiii, Thanks !

Narayan - Thanks !

Hi Luke, 3 mails are getting created in one go !!!

No. the cursor should not move in to any of the cells (left,right or down) after typing the input & press enter in A1

Hi Masters, My worksheet produce the desired result on the input given in Cell A1 by the users. (This is being done with many paramtrs). Here I need to restrict the cursor movement in cell A1 itself. Any kind of xls or VBA function will do. Thanks in advance. Shibu

Topic was good.... discussions were heated ..... But I fear, will it create two fractions in team Ninjas ..... A kind of grading ..... Though all of us (including the Ninjas & the Key Master) sitting in different parts of the world, while we are in the forum, we are like working in one...

Hi Narayan,I checked it with the same data. And it gave me the desired result. Though I didn't get how the formula is worked.

Good Morning Luke, No it is not working or Im not expert in Filtering..... Could you please guide me ?

Hi, Once again.... I have a worksheet with cheque status. The results coming in to the worksheet is produced thru vlookup command. How can I autofilter a worksheet avoiding blank rows ? (I used IFFERROR formula to make the column blank.) Regards, Shibu

If I want to supress the 'zero" ?

Hi Luke, Sorry for the delay.... Though I have made a mistake in giving the field name, your suggestion made me to rework on the criteria and it is being resolved..... Thanks a lot, Luke !