Recent content by shibulal

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...