Hi


I been poundering this for a couple weeks so far. I would like to get
this formula to across multiple worksheets.

=INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9= C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"), ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&" '!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


But no luck.

Let me know if its possible.

Fin Fang Foom wrote:
Hi


I been poundering this for a couple weeks so far. I would like to get
this formula to across multiple worksheets.

=INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9= C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"), ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&" '!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


But no luck.

Let me know if its possible.

Any help ?




Fin Fang Foom wrote:
Hi


I been poundering this for a couple weeks so far. I would like to get
this formula to across multiple worksheets.

=INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9= C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"), ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&" '!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


But no luck.

Let me know if its possible.

Bump!




Fin Fang Foom wrote:
Any help ?




Fin Fang Foom wrote:
Hi


I been poundering this for a couple weeks so far. I would like to get
this formula to across multiple worksheets.

=INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9= C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"), ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&" '!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


But no luck.

Let me know if its possible.

Bump!



Fin Fang Foom wrote:
Bump!




Fin Fang Foom wrote:
Any help ?




Fin Fang Foom wrote:
Hi


I been poundering this for a couple weeks so far. I would like to get
this formula to across multiple worksheets.

=INDEX(Sheet2!$A$2:$A$9,SMALL(IF(Sheet2!$B$2:$B$9= C2,ROW(Sheet2!$B$2:$B$9)-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"), ROW(INDIRECT("A2:A9"))-2,0,1)),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&" '!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))


But no luck.

Let me know if its possible.

Fin Fang Foom wrote...
....
So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9") ,ROW(INDIRECT("A2:A9"))-2,0,1)),
SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9 "),ROW(INDIRECT("B2:B9"))-2,0,1))
=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))

But no luck.
....

OFFSET is the problem. It won't accept as 1st argument an array of
range references in different worksheets. If you want to convert a 3D
block of cells into a 2D array, you have to use INDIRECT. There is no
alternative short of using add-ins.

For example,

T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1 :A"&(10*COUNTA(WSLST))))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10))))

converts A1:A10 from the worksheets listed in WSLST into a 2D range.

Thank You for replying Harlan Grove. Forgive me for I dont understand
what you are saying. How would I incorporate what you are saying?

Should I change the formula into this?


=INDEX(T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIR ECT("A1:A"&(10*COUNTA(WSLST))))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10)))),SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2& "'!B2:B9"),ROW(INDIRECT("B2:B9"))-2,0,1))=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))



Harlan Grove wrote:
Fin Fang Foom wrote...
...
So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9") ,ROW(INDIRECT("A2:A9"))-2,0,1)),
SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9 "),ROW(INDIRECT("B2:B9"))-2,0,1))
=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))

But no luck.
...

OFFSET is the problem. It won't accept as 1st argument an array of
range references in different worksheets. If you want to convert a 3D
block of cells into a 2D array, you have to use INDIRECT. There is no
alternative short of using add-ins.

For example,

T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1 :A"&(10*COUNTA(WSLST))))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10))))

converts A1:A10 from the worksheets listed in WSLST into a 2D range.

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.

2) It returns #NUM! in some instances.

Nevertheless, try it and see if it works for you. Assuming that A2:B9
on each sheet contains your data, list the sheet 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))))

Lastly, try...

=INDEX(T(INDIRECT("'"&INDEX(WSLST,Array1)&"'!A"&(A rray2))),SMALL(IF(N(IND
IRECT("'"&INDEX(WSLST,Array1)&"'!B"&(Array2)))=C2, Array3),COUNTIF($C$2:C2
,C2)))

....confirmed with CONTROL+SHIFT+ENTER. Does this work for you?

In article .com,
"Fin Fang Foom" wrote:

Thank You for replying Harlan Grove. Forgive me for I dont understand
what you are saying. How would I incorporate what you are saying?

Should I change the formula into this?


=INDEX(T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIR ECT("A1:A"&(10*COUNTA(WSLST)
)))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10)))),SMALL(IF(
N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(IND IRECT("B2:B9"))-2,0,1))=C2,R
OW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))



Harlan Grove wrote:
Fin Fang Foom wrote...
...
So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9") ,ROW(INDIRECT("A2:A9"))-2,
0,1)),
SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9 "),ROW(INDIRECT("B2:B9"))-
2,0,1))
=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))

But no luck.
...

OFFSET is the problem. It won't accept as 1st argument an array of
range references in different worksheets. If you want to convert a 3D
block of cells into a 2D array, you have to use INDIRECT. There is no
alternative short of using add-ins.

For example,

T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1 :A"&(10*COUNTA(WSLST))))-1
)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10))))

converts A1:A10 from the worksheets listed in WSLST into a 2D range.

Hi Domenic,


I implemented your solution and it seems to only retrieve values in
sheet2. It will not retrieve values from sheet3.

I dont know what I'm missing.



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.

2) It returns #NUM! in some instances.

Nevertheless, try it and see if it works for you. Assuming that A2:B9
on each sheet contains your data, list the sheet 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))))

Lastly, try...

=INDEX(T(INDIRECT("'"&INDEX(WSLST,Array1)&"'!A"&(A rray2))),SMALL(IF(N(IND
IRECT("'"&INDEX(WSLST,Array1)&"'!B"&(Array2)))=C2, Array3),COUNTIF($C$2:C2
,C2)))

...confirmed with CONTROL+SHIFT+ENTER. Does this work for you?

In article .com,
"Fin Fang Foom" wrote:

Thank You for replying Harlan Grove. Forgive me for I dont understand
what you are saying. How would I incorporate what you are saying?

Should I change the formula into this?


=INDEX(T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIR ECT("A1:A"&(10*COUNTA(WSLST)
)))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10)))),SMALL(IF(
N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(IND IRECT("B2:B9"))-2,0,1))=C2,R
OW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))



Harlan Grove wrote:
Fin Fang Foom wrote...
...
So I tried to modify into this:

=INDEX(T(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!A2:A9") ,ROW(INDIRECT("A2:A9"))-2,
0,1)),
SMALL(IF(N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9 "),ROW(INDIRECT("B2:B9"))-
2,0,1))
=C2,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))

But no luck.
...

OFFSET is the problem. It won't accept as 1st argument an array of
range references in different worksheets. If you want to convert a 3D
block of cells into a 2D array, you have to use INDIRECT. There is no
alternative short of using add-ins.

For example,

T(INDIRECT("'"&INDEX(WSLST,1+INT((ROW(INDIRECT("A1 :A"&(10*COUNTA(WSLST))))-1
)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(W SLST))))-1,10))))

converts A1:A10 from the worksheets listed in WSLST into a 2D range.

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((R OW(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";"t he";"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 sheet contains your data, list the sheet 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 worksheet blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing worksheet 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,C 2))+1)

Hi Harlan,

I implemented a your solutions and I'm getting a #REF! error. Maybe
this might be the problem. When you say

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

I defined it the number 8 in my named ranges. Or else I dont know what
I'm missing.



Harlan Grove 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((R OW(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";"t he";"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 sheet contains your data, list the sheet 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 worksheet blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing worksheet 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,C 2))+1)

I'am running Excel 2003.

Here how I set it up.


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))))
N =8
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3

and using this formula:

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

Am I missing something?


Harlan Grove 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((R OW(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";"t he";"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 sheet contains your data, list the sheet 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 worksheet blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing worksheet 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,C 2))+1)

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

In article .com,
"Harlan Grove" 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((R OW(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";"t he";"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 sheet contains your data, list the sheet 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 worksheet blocks a defined name on it's own. So something like

WSLST: a single column, multiple row range containing worksheet 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,C 2))+1)