SMALL(IF( across multiple worksheets
Sorry Harlan, I just noticed your recent post where I think you've
addressed the problem. I'll be taking a closer look at it...
Thanks!
In article ,
Domenic wrote:
Harlan,
If I follow the instructions for Part 1 of your post, I have no problem.
It works beautifully. I can select Sheet3!A1:A16, enter the array
formula, and it returns the correct values.
It's when I try to fit it into the OP's original formula that defined
names need to be used and that the formula doesn't seem to work. Here's
the situation as I understand it...
Sheet1!A2:A9 contains:
{"A";"B";"C";"D";"E";"F";"G";"H"}
Sheet1!B2:B9 contains:
{10;12;11;18;12;20;26;28}
Sheet2!A2:A9 contains:
{"I";"J";"K";"L";"M";"N";"O";"P"}
Sheet2!B2:B9 contains:
{20;10;18;20;12;14;14;16}
Sheet3!C2:C17 contains:
{10;10;11;12;12;12;14;14;16;18;18;20;20;20;26;28}
Defined names:
N:
8
S:
=ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
Col_A:
=T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!A"&(2+MOD(S,N))))
Col_B:
=N(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N))))
Lastly, the following formula is entered in Sheet3!D2, and copied down:
=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C 2))+1)
...which returns...
A
A
C
B
E
B
#NUM!
#NUM!
#NUM!
D
D
F
F
#NUM!
G
H
As you can see, the formula doesn't seem to work. It doesn't return the
corresponding values in Column A of Sheet1 and Sheet2 for Column C in
Sheet3. At least not on my version of Excel (Macintosh Excel v.X).
Now, for the $64,000 question. :) Does this work on your system?
By the way, I like how you've defined the formulas. It allows the final
formula to look a lot nicer...
|