=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!
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...
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 ?
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 <=actual value "line chart color is green),(projected projected value >= 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.
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,
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
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 !