Lookups referencing tab names

[Razar]

I am looking for a formula that will combine a lookup by tab name and sumif. The sumif part I can handle, but how can I lookup to a specific tab? For example, if I list all of my tab names in column A, I want my formula to find that tab and perform my sumif function. Please help...

 

[Luke M]

Check out the INDIRECT function. Let's you combine text strings to create references. So, perhaps something like:

=SUM(INDIRECT(A2&"!A:A")

Would sum everything in column of the worksheet whose name is listed in A2. If the sheet name has a space in it, be sure to include a pair of single quotation marks.

=SUM(INDIRECT("'"&A2&"'!A:A")

 

[Montrey]

Use the indirect function Razar.

=INDIRECT("'" & A2 & "'!" & B2)


More info here.

http://www.contextures.com/xlFunctions05.html

 

[Razar]

I guess I need more of a cheat...

On my summary tab in cell A2, I have 20-010 (the tab I want to reference). In cell B2, I need my formula to say For tab "20-010", if column B contains "Aug-12", sum column F.

 

[Luke M]

=SUMIF(INDIRECT(A2&"!B:B"),"Aug-12",INDIRECT(A2&"!F:F))


Structurally, that will be the correct formula. If you have real, complete dates in column B, you'll need to probably do something else for the criteria argument.

 

[SirJB7]

Hi, Razar!

This is a not slightly different question than your first post.

Try this:

=SUMAR.SI.CONJUNTO(INDIRECTO(A2&"!F:F");INDIRECTO(A2&"!B:B");">="&FECHA(2012;8;1);INDIRECTO(A2&"!B:B");"<="&FECHA(2012;8;31)) -----> in english: =SUMIFS(INDIRECT(A2&"!F:F"),INDIRECT(A2&"!B:B"),">="&DATE(2012,8,1),INDIRECT(A2&"!B:B"),"<="&DATE(2012,8,31))

Regards!