Vlookup and Sum Across Multiple Tabs?

[Dean99]

Hi Everyone-

I currently have a tax workbook with 50+ tabs (at least one for each State), and in each tab there are tables with many divisions and the amount of taxes paid. I'm trying to figure out a way to do a vlookup and sum in a main summary worksheet such that I can get the total taxes paid by each division (it would need to vlookup each of the 50+ tabs, and then sum). Is there a way to do this? I'm not very good with VBA so please be gentle if that's what is required . . .

Thanks in advance for your help!

 

[daniels012]

Or if you have at the top of your page:
Manager A and total from that page with:

Excel 2007
	A	B	C	D	E	F	G
2		Manager A	930	Manager B	325	Manager C	125
3
4	Manager A	900
5	Manager A	15
6	Manager B	25
7	Manager C	25
8	Manager A	15
9	Manager B	200
10	Manager B	100
11	Manager C	100
Sheet1

Cell Formulas
Range	Formula
C2	=SUMIF($A$4:$A$11,B2,$B$4:$B$11)
E2	=SUMIF($A$4:$A$11,D2,$B$4:$B$11)
G2	=SUMIF($A$4:$A$11,F2,$B$4:$B$11)

Then use a formula I listed before.
That is easeist

 

[Dean99]

I think I'm doing something wrong . . . I defined list to be

=Sheet1!$A$1:$B$10,Sheet2!$A$1:$B$10,Sheet3!$A$1:$B$10

and then I typed in the formula on my summary tab as

=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A1:A10"),A1,INDIRECT("'"&list&"'!B1:B10")))

What am I doing wrong? I apologize if I'm not getting this fast enough . . .

 

[Robert Mika]

Your list should be in one column, as one range.
Please see my example-the last line in my previous post.

 

[Dean99]

Ok I'm still not getting it, but I understand conceptually . . . why can't I just do it this way?

Define Test1 =Sheet1!$A:$A,Sheet2!$A:$A
Define Test2 =Sheet1!$B:$B,Sheet2!$B:$B

Then in my summary tab, to sum A for sheets 1 and 2 I use

=SUMIF(Test1,"A",Test2)

When I try that, I get an error . . . why?

 

[Robert Mika]

You are listing sheets name not the ranges.
Why you are getting error?
Becouse SUMIF does not work this why..I 'm sure there is not formula that work like this.(multiple ranges)

 

[Dean99]

Is there any way you can send me a working spreadsheet so I can see what you are doing in action? I seriously cannot figure out what I'm doing wrong . . . .

 

[Robert Mika]

Example:
http://dl.dropbox.com/u/78583609/example.xlsx

 

[Dean99]

Thanks you rock!

 

[Robert Mika]

You are welcome.

 

[gypsy1914]

If you had faloow the links I have posted you woudl get this
Excel 2010

	A	B
Manager	Sales
Manager A
Manager B
Manager C

<colgroup><col style="background-color: #DAE7F5"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$900 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$600 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$500 [/TD]

</tbody>

Sheet1​

Excel 2010

	A	B
Manager	Sales
Manager C
Manager D
Manager A

<colgroup><col style="background-color: #DAE7F5"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$200 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$500 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$100 [/TD]

</tbody>

Sheet2​

Excel 2010

	A	B
Manager	Sales
Manager C
Manager A
Manager D

<colgroup><col style="background-color: #DAE7F5"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$200 [/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$5 [/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$70 [/TD]

</tbody>

Sheet3​

Excel 2010

	A	B	C	D	E	F
Manager	Sales
Manager A	Sheet1
Manager B	Sheet2
Manager C	Sheet3
Manager D

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]600[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]900[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]570[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A2:A100"),A2,INDIRECT("'"&list&"'!B2:b100 ")))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]list[/TH]
[TD="align: left"]=Summary!$F$2:$F$4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to do the same thing only summing the daily totals of my business expenses. Each day being a sheet and a monthly summary sheet. You lost me at &List&. I downloaded the file you uploaded to drop box as well. I understand making the list of sheets, which I did. My question is how do I define that as my &List&?