Lookup mulitple tabs

Hi there -
How can I get a vertical lookup or sumIF formula to check multiple tabs for a given value?

Or - is there a way to specify the tab? For instance, put "Tab A" or "Tab B" in Cell A1, and have the lookup formula reference the value of Cell A1.

Thanks for your help,
Jill

How many tabs? What are their *REAL* names? What is the range of interest
(eg - A1:B10)?

Biff

"jillteresa" <jillteresa.2b3pyk_1153159512.9458@excelforum-nospam.com> wrote
in message news:jillteresa.2b3pyk_1153159512.9458@excelforum-nospam.com...
>
> Hi there -
> How can I get a vertical lookup or sumIF formula to check multiple tabs
> for a given value?
>
> Or - is there a way to specify the tab? For instance, put "Tab A" or
> "Tab B" in Cell A1, and have the lookup formula reference the value of
> Cell A1.
>
> Thanks for your help,
> Jill
>
>
> --
> jillteresa
> ------------------------------------------------------------------------
> jillteresa's Profile:
> http://www.excelforum.com/member.php...fo&userid=9498
> View this thread: http://www.excelforum.com/showthread...hreadid=562167
>

How many tabs? What are their *REAL* names? What is the range of interest
(eg - A1:B10)?

Biff

"jillteresa" <jillteresa.2b3pyk_1153159512.9458@excelforum-nospam.com> wrote
in message news:jillteresa.2b3pyk_1153159512.9458@excelforum-nospam.com...
>
> Hi there -
> How can I get a vertical lookup or sumIF formula to check multiple tabs
> for a given value?
>
> Or - is there a way to specify the tab? For instance, put "Tab A" or
> "Tab B" in Cell A1, and have the lookup formula reference the value of
> Cell A1.
>
> Thanks for your help,
> Jill
>
>
> --
> jillteresa
> ------------------------------------------------------------------------
> jillteresa's Profile:
> http://www.excelforum.com/member.php...fo&userid=9498
> View this thread: http://www.excelforum.com/showthread...hreadid=562167
>

Something like this

=SUMPRODUCT(--(Tab1!A1:A10=Sheet1!A1)*(Tab1!A1:A10)+(--(Tab2!A1:A10=Sheet1!A1)*(Tab2!A1:A10)))

OR

=IF(INDIRECT(A1&"!A1")=B1,SUM(Tab1!A1:A10)+SUM(Tab2!A1:A10),"Criteria not matched")

VBA Noob

Thanks!
Real names:
I need a series of information associated with code 1004. That code could either be located on tabs Q1, Q2, Q3, Q4. I'd like to set up a formula that could default check all tabs. The other option would be entering the Quarter into another cell and setting up the formula to read that cell. So, if I know it is a Q2 code, I can put "Q2" somewhere else in the sheet and the formulas would only check the Q2 tab.

Make sense?
Jill

Sorry - forgot to add the lookup range would probably be the whole sheet since info is constantly getting added: $A:$G.

Here's one way:

Return the sheet name where 1004 is in the range A1:A10. Sheet names are Q1,
Q2, Q3 and Q4.

Entered as an array using the key combination of CTRL,SHIFT,ENTER:

=INDEX({"Q1","Q2","Q3","Q4"},MATCH(TRUE,COUNTIF(INDIRECT("'Q"&ROW(INDIRECT("1:4"))&"'!A1:A10"),A1)>0,0))

Another way:

List the sheet names in some range of cells:

H1 = Q1
H2 = Q2
H3 = Q3
H4 = Q4

Also array entered:

=INDEX(H1:H4,MATCH(TRUE,COUNTIF(INDIRECT("'"&H1:H4&"'!A1:A10"),A1)>0,0))

Or, you can do a direct vlookup or sumif but you haven't provided enough
details to put that together.

Biff

"jillteresa" <jillteresa.2b3tw7_1153164613.7227@excelforum-nospam.com> wrote
in message news:jillteresa.2b3tw7_1153164613.7227@excelforum-nospam.com...
>
> Sorry - forgot to add the lookup range would probably be the whole sheet
> since info is constantly getting added: $A:$G.
>
>
> --
> jillteresa
> ------------------------------------------------------------------------
> jillteresa's Profile:
> http://www.excelforum.com/member.php...fo&userid=9498
> View this thread: http://www.excelforum.com/showthread...hreadid=562167
>