Re: SMALL(IF( across multiple worksheets

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 *** 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))))

Lastly, try...

=INDEX(T(INDIRECT("'"&INDEX(WSLST,Array1)&"'!A"&(Array2))),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 <1162842765.443527.188430@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>,
"Fin Fang Foom" <vj.contreras@xxxxxxxxx> 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(INDIRECT("A1:A"&(10*COUNTA(WSLST)
)))-1)
/10))&"'!A"&(1+MOD(ROW(INDIRECT("A1:A"&(10*COUNTA(WSLST))))-1,10)))),SMALL(IF(
N(OFFSET(INDIRECT("'"&$A$2:$B$2&"'!B2:B9"),ROW(INDIRECT("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(WSLST))))-1,10))))

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

Loading