Lookup mulitple tabs

[jillteresa]

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

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

Thanks for your help,
Jil

 

[Biff]

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

Biff

 

[VBA Noob]

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

 

[jillteresa]

Thanks!
Real names:
I need a series of information associated with code 1004. That cod
could either be located on tabs Q1, Q2, Q3, Q4. I'd like to set up
formula that could default check all tabs. The other option would b
entering the Quarter into another cell and setting up the formula t
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 Q
tab.

Make sense?
Jil

 

[jillteresa]

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

 

[Biff]

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