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]

Welcome to the board!

A formula like this would work:

=SUM(Sheet2!A1,Sheet4!A1)

As long as the total is in the same cell on every sheet

Michael

 

[Robert Mika]

If data in your sheets have the same layout you may look at this topics:
http://www.mrexcel.com/forum/showthread.php?p=2983726

http://www.mrexcel.com/forum/showthread.php?p=3095627

 

[Dean99]

Hi Guys-

Thanks for your reply! However, the values I'm summing are now always in the same cell . . . although , they will be in the same column (hence, I was trying to do a vlookup). Is this possible? Thanks!

 

[BlueHornet]

Something like that sounds like it would be simpler with Named Ranges:

=Sum(Alabama!SalesTax + Alaska!SalesTax + ... + Wyoming!SalesTax)

Other than that (pretty simplistic) idea, can you give an example of what you're trying to accomplish?

 

[Robert Mika]

Hi Guys-

Thanks for your reply! However, the values I'm summing are now always in the same cell . . . although , they will be in the same column (hence, I was trying to do a vlookup). Is this possible? Thanks!

Did you try to look at the link I have posted?
VLOOKUP will not give you SUM from the sheets(unless nested(VLOOKUP()+VLOOKUP()....))

To post data:
You can download and install two of the following programs:

HTLMaker

or
Excel Jeanie


or when using Internet Explorer just put borders around your data in Excel and copy those cells into your post.

 

[Dean99]

Thanks Guys . . . BlueHornet, I thought about doing it that way, but I wanted to avoid having to manually sum cells in 50 worksheets . . . also, if I change the order of one then I will have to redo the entire formula.

I tried downloading those two programs but I think the firewall here at work is preventing me from downloading them. But lets say I have a simple worksheet where

Sheet1 I have a table like this:

Manager A $900
Manager B $600
Manager C $500

In Sheet 2 I have another table like this:

Manager C $200
Manager D $500
Manager A $100

In Sheet 3 I have:

Manager C $200
Manager A $5
Manager D $70

Can I now create a summary sheet that will do a vlookup to each of the managers in each sheet and sum their total amounts?

 

[jambo72uk]

Thanks Guys . . . BlueHornet, I thought about doing it that way, but I wanted to avoid having to manually sum cells in 50 worksheets . . . also, if I change the order of one then I will have to redo the entire formula.

I tried downloading those two programs but I think the firewall here at work is preventing me from downloading them. But lets say I have a simple worksheet where

Sheet1 I have a table like this:

Manager A $900
Manager B $600
Manager C $500

In Sheet 2 I have another table like this:

Manager C $200
Manager D $500
Manager A $100

In Sheet 3 I have:

Manager C $200
Manager A $5
Manager D $70

Can I now create a summary sheet that will do a vlookup to each of the managers in each sheet and sum their total amounts?

I have created a sheet with 4 tabs. first Tab is "Summary" and the other three are "sheet1""sheet2""sheet3" In these sheets i have Manager in column A and $ in Column B. The Summary sheet is exactly the same and i have entered the following formula to calculate the managers totals over the 3 sheets.

=SUMIF(Sheet1!A:A,Summary!A1,Sheet1!B:B)+SUMIF(Sheet2!A:A,Summary!A1,Sheet2!B:B)+SUMIF(Sheet3!A:A,Summary!A1,Sheet3!B:B)

Thanks

Jamie

 

[jambo72uk]

I have created a sheet with 4 tabs. first Tab is "Summary" and the other three are "sheet1""sheet2""sheet3" In these sheets i have Manager in column A and $ in Column B. The Summary sheet is exactly the same and i have entered the following formula to calculate the managers totals over the 3 sheets.

=SUMIF(Sheet1!A:A,Summary!A1,Sheet1!B:B)+SUMIF(Sheet2!A:A,Summary!A1,Sheet2!B:B)+SUMIF(Sheet3!A:A,Summary!A1,Sheet3!B:B)

Thanks

Jamie

Issue you will have though is you wont be able to input 50 seperate arguments as excel wont allow. Is there no way you can put the date in one tab and use a pivot to calculate it?

Thanks,

Jamie

 

[Robert Mika]

If you had faloow the links I have posted you woudl get this
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Manager</td><td style=";">Sales</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Manager A</td><td style="text-align: right;;">$900 </td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Manager B</td><td style="text-align: right;;">$600 </td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Manager C</td><td style="text-align: right;;">$500 </td></tr></tbody></table>

Sheet1​

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Manager</td><td style=";">Sales</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Manager C</td><td style="text-align: right;;">$200 </td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Manager D</td><td style="text-align: right;;">$500 </td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Manager A</td><td style="text-align: right;;">$100 </td></tr></tbody></table>

Sheet2​

Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Manager</td><td style=";">Sales</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">Manager C</td><td style="text-align: right;;">$200 </td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">Manager A</td><td style="text-align: right;;">$5 </td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">Manager D</td><td style="text-align: right;;">$70 </td></tr></tbody></table>

Sheet3​

Excel 2010
	A	B	C	D	E	F
1	Manager	Sales
2	Manager A	1005				Sheet1
3	Manager B	600				Sheet2
4	Manager C	900				Sheet3
5	Manager D	570
Summary

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A2:A100"),A2,INDIRECT("'"&list&"'!B2:b100 ")))

Named Ranges
Name	Refers To	Cells
list	=Summary!$F$2:$F$4