Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Vlookup and Sum Across Multiple Tabs?

This is a discussion on Vlookup and Sum Across Multiple Tabs? within the Excel Questions forums, part of the Question Forums category; Hi Everyone- I currently have a tax workbook with 50+ tabs (at least one for each State), and in each ...

  1. #1
    New Member
    Join Date
    May 2012
    Posts
    9

    Cool Vlookup and Sum Across Multiple Tabs?

    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!

  2. #2
    Board Regular daniels012's Avatar
    Join Date
    Jan 2005
    Location
    34 56' 19" N / 82 13' 38" W
    Posts
    5,162

    Default Re: Vlookup and Sum Across Multiple Tabs?

    Welcome to the board!

    A formula like this would work:
    Code:
    =SUM(Sheet2!A1,Sheet4!A1)
    As long as the total is in the same cell on every sheet

    Michael
    "It is so nice, to see so many, that know so much"

    Using Excel 2007

  3. #3
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,208
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  4. #4
    New Member
    Join Date
    May 2012
    Posts
    9

    Default Re: Vlookup and Sum Across Multiple Tabs?

    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!

  5. #5
    Board Regular
    Join Date
    Apr 2012
    Posts
    105

    Default Re: Vlookup and Sum Across Multiple Tabs?

    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?

  6. #6
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,208

    Default Re: Vlookup and Sum Across Multiple Tabs?

    Quote Originally Posted by Dean99 View Post
    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.
    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

  7. #7
    New Member
    Join Date
    May 2012
    Posts
    9

    Default Re: Vlookup and Sum Across Multiple Tabs?

    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?

  8. #8
    Board Regular
    Join Date
    May 2012
    Posts
    56

    Default Re: Vlookup and Sum Across Multiple Tabs?

    Quote Originally Posted by Dean99 View Post
    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

  9. #9
    Board Regular
    Join Date
    May 2012
    Posts
    56

    Default Re: Vlookup and Sum Across Multiple Tabs?

    Quote Originally Posted by jambo72uk View Post
    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

  10. #10
    MrExcel MVP Robert Mika's Avatar
    Join Date
    Jun 2009
    Location
    (current) UK, (origin) Poland
    Posts
    7,208

    Default Re: Vlookup and Sum Across Multiple Tabs?

    If you had faloow the links I have posted you woudl get this
    Excel 2010
    AB
    1ManagerSales
    2Manager A$900
    3Manager B$600
    4Manager C$500
    Sheet1




    Excel 2010
    AB
    1ManagerSales
    2Manager C$200
    3Manager D$500
    4Manager A$100
    Sheet2



    Excel 2010
    AB
    1ManagerSales
    2Manager C$200
    3Manager A$5
    4Manager D$70
    Sheet3
    Excel 2010
    ABCDEF
    1ManagerSales
    2Manager A1005Sheet1
    3Manager B600Sheet2
    4Manager C900Sheet3
    5Manager D570

    Summary



    Worksheet Formulas
    CellFormula
    B2=SUMPRODUCT(SUMIF(INDIRECT("'"&list&"'!A2:A100"),A2,INDIRECT("'"&list&"'!B2:b100 ")))

    Workbook Defined Names
    NameRefers To
    list=Summary!$F$2:$F$4

    - Read the Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
    - If posting vba code, please use Code Tags .

Page 1 of 3 123 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com