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" wrote
in message ...

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(Tab 2!A1:A10),"Criteria
not matched")

VBA Noob


--
VBA Noob
------------------------------------------------------------------------
VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
View this thread: http://www.excelforum.com/showthread...hreadid=562167

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


--
jillteresa
------------------------------------------------------------------------
jillteresa's Profile: http://www.excelforum.com/member.php...fo&userid=9498
View this thread: http://www.excelforum.com/showthread...hreadid=562167

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

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(IN DIRECT("'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" wrote
in message ...

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