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.