Re: SMALL(IF( across multiple worksheets

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,C2))+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...

In article <1162920828.737190.144090@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
"Harlan Grove" <hrlngrv@xxxxxxx> wrote:

Domenic wrote...
I tried adopting Harlan's approach for your solution, but came across a
couple of issues...

1) Excel will not accept the formula unless defined names are used.

Perhaps this is version-dependent, but under Excel 2003 SP1 with the
following in Sheet1!A2:A9,
{"this";"is";"a";"test";"now";"is";"the";"time"}, and the following in
Sheet2!A2:A9,
{"the";"quick";"brown";"fox";"jumped";"over";"the";"lazy"}, I select
Sheet3!A1:A16 and enter the array formula

=T(INDIRECT("'"&INDEX({"Sheet1";"Sheet2"},1+INT((ROW(INDIRECT("A1:A"&
(8*COUNTA({"Sheet1";"Sheet2"}))))-1)/8))&"'!A"&(2+MOD(ROW(INDIRECT("A1:A"
&(8*COUNTA({"Sheet1";"Sheet2"}))))-1,8))))

and it returns
{"this";"is";"a";"test";"now";"is";"the";"time";"the";"quick";"brown";"fox";
"jumped";"over";"the";"lazy"} as expected.

What was your exact formula that appeared to require defined names? Do
you mean fitting this into the OP's original formula requires defined
names in order to avoid the 7 nested function call limit? That's
likely.

2) It returns #NUM! in some instances.

Such as?

Nevertheless, try it and see if it works for you. Assuming that A2:B9
on each *** contains your data, list the *** names in a range of
cells and name this range WSLST. Then define the following...

Array1:
=1+INT((ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1)/8)

Array2:
=2+MOD(ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))-1,8)

Array3:
=ROW(INDIRECT("1:"&(8*COUNTA(WSLST))))

If it were me, I'd define the last of these first then use it in the
definition of the other two. I'd probably also make each of the OP's
multiple work*** blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing work*** names

N: the number of rows in the common ranges in each of the worksheets
in WSLST

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:
=T(INDIRECT("'"&INDEX(WSLST,1+INT(S/N))&"'!B"&(2+MOD(S,N))))

then make the array formula

=INDEX(Col_A,SMALL(IF(Col_B=C2,S),COUNTIF(C$2:C2,C2))+1)
.