Re: SMALL(IF( across multiple worksheets

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