SMALL(IF( across multiple worksheets

Fin Fang Foom

unread,

Nov 2, 2006, 8:06:56 PM11/2/06

to

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

unread,

Nov 3, 2006, 12:16:00 PM11/3/06

to

Fin Fang Foom

unread,

Nov 3, 2006, 12:16:29 PM11/3/06

to

Any help ?

Fin Fang Foom

unread,

Nov 4, 2006, 12:34:31 PM11/4/06

to

Bump!

Fin Fang Foom

unread,

Nov 6, 2006, 8:53:53 AM11/6/06

to

Harlan Grove

unread,

Nov 6, 2006, 11:41:43 AM11/6/06

to

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.

Fin Fang Foom

unread,

Nov 6, 2006, 2:52:45 PM11/6/06

to

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,ROW(INDIRECT("'"&$A$2:$B$2&"'!A2:A9"))-ROW(A$2)+1),COUNTIF(C$2:C2,C2)))

Domenic

unread,

Nov 7, 2006, 8:10:12 AM11/7/06

to

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"&(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.4...@b28g2000cwb.googlegroups.com>,

"Fin Fang Foom" <vj.con...@yahoo.com> 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

Fin Fang Foom

unread,

Nov 7, 2006, 9:37:51 AM11/7/06

to

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.

Harlan Grove

unread,

Nov 7, 2006, 12:33:48 PM11/7/06

to

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

Fin Fang Foom

unread,

Nov 7, 2006, 1:16:11 PM11/7/06

to

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.

Fin Fang Foom

unread,

Nov 7, 2006, 1:24:00 PM11/7/06

to

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

Am I missing something?

Harlan Grove

unread,

Nov 7, 2006, 1:24:12 PM11/7/06

to

Fin Fang Foom wrote...

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

Yes, N should refer to 8. That can be checked: the formula =N should
return 8. Did you name your list of worksheet names WSLST? If so, what
are your actual worksheet names?

Harlan Grove

unread,

Nov 7, 2006, 1:28:20 PM11/7/06

to

Fin Fang Foom wrote...
...

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

...

These are problems. The first literal strings should be " ' " (without
the spaces around the single quote/apostrophe) rather than " ". This is
a purely protective measure. If your worksheet names don't contain
spaces or hyphens, you don't need to put single quotes around them, but
it doesn't hurt when they're not needed, and it prevents errors when
they are needed.

Fin Fang Foom

unread,

Nov 7, 2006, 2:33:50 PM11/7/06

to

Hi Harlan,

I maded the modifications to this:

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

Now it gining me a #NUM! error.

Are the above name ranges are correct?

Harlan Grove

unread,

Nov 7, 2006, 3:57:31 PM11/7/06

to

Fin Fang Foom wrote...

>I maded the modifications to this:
>
>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))))

...

>Now it gining me a #NUM! error.
>
>Are the above name ranges are correct?

Above definitions are correct possibly except for Col_B. Do your column
B ranges contain text or numbers? If numbers, you need to change the
T(..) call to an N(..) call.

However, there's a problem. The Col_A and Col_B defined name formulas
work when entered directly into multiple cell ranges, but not as terms
in longer formulas. Yet another defined name is needed.

XWSLST:
=T(OFFSET(WSLST,INT(S/N),0,1,1))

then change the defintions of Col_A and Col_B to

Col_A:
=T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_B:
=T(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if text
=N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N)))) if numeric

The array formula

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

would still return #NUM! when there are no instances of the C2 value in
Col_B. If you want to trap such errors, try something like

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

Domenic

unread,

Nov 7, 2006, 4:26:56 PM11/7/06

to

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.7...@k70g2000cwa.googlegroups.com>,

Domenic

unread,

Nov 7, 2006, 4:31:10 PM11/7/06

to

Sorry Harlan, I just noticed your recent post where I think you've
addressed the problem. I'll be taking a closer look at it...

Thanks!

In article <domenic22-2411F...@msnews.microsoft.com>,

Domenic

unread,

Nov 7, 2006, 4:49:06 PM11/7/06

to

Harlan, this works beautifully! Thanks very much!

Cheers!

In article <1162933051.7...@m73g2000cwd.googlegroups.com>,

Message has been deleted

Fin Fang Foom

unread,

Nov 7, 2006, 5:04:29 PM11/7/06

to

Hi Harlan,

It looks like it's working I need to test it a couple of times just to
make sure but it looks good. The solution you provided is very handy
when doing a lookup across worksheets that accounts for duplicates.
This what I have:

Col_A =T(INDIRECT("'"&XWSLST&"'!A"&(2+MOD(S,N))))

Col_B =N(INDIRECT("'"&XWSLST&"'!B"&(2+MOD(S,N))))

N =8
S =ROW(INDIRECT("1:"&(N*ROWS(WSLST))))-1
WSLST =Sheet1!$A$2:$A$3

XWSLST =T(OFFSET(WSLST,INT(S/N),0,1,1))

and using this formula

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

an-array

Ctrl,Shift,Enter

Harlan when you get a chance can you explain how all these formula
work?