Array Formula Assistance

[3G]

Hello-

I'm thinking an array formula is in order here...but having issues with getting mine to work.

Here's my current formula:

=ROWS($K$18:$K18)&". "&IF(ISNUMBER(Sheet3!Q2:Q18),INDEX(Sheet3!C2:C18,(Sheet3!Q2:Q18)))


What I'm trying to do is produce a list of 6 items on a dashboard page, by scannig a list where Column Q is a "rank" (1-6),and, if it IS a number, bring back the contents of Cell C. If it's not ranked, it says "no". There are only unique values (you'll never rank 2 different things the same)


The results I currently get is only "FALSE", and, not really sure if that's valid


Any help is appreciated.


Thanks

3g

 

[fred]

need a ")" after "index(sheet3!c2:c18" ????

 

[3G]

Sorry Fred...the formula got messed up in my pasting...

=ROWS($K$18:$K18)&". "&IF(ISNUMBER(Sheet3!Q2:Q18),INDEX(Sheet3!C2:C18,MATCH(K18,Sheet3!Q2:Q18,0)))


Thanks

 

[fred]

isnumber only for one number not a group of numbers.

 

[Luke M]

Try this array in K18:

=IF(ROWS($K$18:K18)>COUNT(Q:Q),"",INDEX(C:C,SMALL(IF(ISNUMBER($Q$2:$Q$18),ROW($Q$2:$Q$18)),ROW(A1))))


Copy down 6 cells. Any unused formulas should display "".

 

[3G]

Thanks Luke!


ROWS(K18:K18) is on the dashboard page...the other data is on a page titled "November Issues". Here's what the formula looks like:

=IF(ROWS($K$18:K18)>COUNT('November Issues'!Q:Q),"",INDEX('November Issues'!C:C,SMALL(IF(ISNUMBER('November Issues'!$Q$2:$Q$18),ROW('November Issues'!$Q$2:$Q$18)),ROW(A1))))


It comes back with a #NUM! error when I Ctrl+Shft+Ent, but, does work right off the bat. Any ideas?

 

[Luke M]

Strange, it's working fine on my workbook...

can you clarify you last sentence?

It comes back with a #NUM! error when I Ctrl+Shft+Ent, but, does work right off the bat. Any ideas?

 

[3G]

Sure. when i paste it, i get a result, but, it's not a valid result. It brings back a value in Column C that is not a ranked item. Then, when I press control shift & enter, it changes to the NUM error.

 

[Luke M]

Hmm. Thinking in debug mode...the #NUM error is indicating that an argument that requires a number is not getting a number. The only function in the formula is the SMALL function. However, it should just be being fed the row numbers.


Could you try using the evaluate formula tool? From the Formula Audting toolbar, it's the button that looks like a magnifying glass? There must be something that's making it's way into the formula...


Other side notes, I'm using 2003. Is this the same for you?

 

[3G]

Got it to work! Turns out the rankings in the Q column were formatted as text, not numbers.


Luke, I thank you my friend. Would love to see Hui break this apart in his formula forensics series!!


3G

 

[Luke M]

Thanks 3G. I actually wrote up an article for Hui's series on this formula, should hopefully see it soon. =)

I also want to thank you for asking about it on Hui's earlier post...it was the final encouragement I needed to give writing a try.