Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I'm dealing with a company with over 20 owners who receive payments every pay period. They take 10% of the payment to leave in the business. Each pay period is on a separate tab in excel. I'm trying to come up with an easy way to track how much each owner has been paid, and how much they've left in the business (10%) cumulatively across the tabs. Could I use a pivot table to do this? Or Vlookup somehow? I guess I'm stuck because it's across so many tabs, and the tabs for the pay periods that haven't happened yet haven't been created yet. Thanks!
Solved by V. L. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 28/08/2018 - 05:37
hello?
Excelchat Expert 28/08/2018 - 05:37
Welcome, Thanks for choosing Got It Pro-Excel.
User 28/08/2018 - 05:38
hi, thanks.
Excelchat Expert 28/08/2018 - 05:38
Do you have sample data that we can work with?
User 28/08/2018 - 05:40
I'll populate the google sheet with a sample of what my data looks like, except my real sheet is bigger
Excelchat Expert 28/08/2018 - 05:40
Okay.
User 28/08/2018 - 05:41
ok that's roughly what it looks like
User 28/08/2018 - 05:41
so i'm trying to figure out an easy way to total columns B C and D across the tabs
User 28/08/2018 - 05:42
and so that when more tabs get added for the pay periods yet to come, they also get included in the totals
User 28/08/2018 - 05:42
i was thinking about a pivot table, but i don't really know how to make that work across tabs
User 28/08/2018 - 05:43
or maybe vlookup? but again, same problem
Excelchat Expert 28/08/2018 - 05:43
Okay.
Excelchat Expert 28/08/2018 - 05:43
Firstly, do the number of sheets keep being enlarged?
User 28/08/2018 - 05:44
all the data for 2018 is in the same sheet, with 26 different tabs for each pay period
User 28/08/2018 - 05:44
a new sheet will be made in 2019
Excelchat Expert 28/08/2018 - 05:45
So we will need a summary sheet.
Excelchat Expert 28/08/2018 - 05:45
Which contains all the details for each person.
Excelchat Expert 28/08/2018 - 05:46
Kindly check the summary sheet, I have added.
Excelchat Expert 28/08/2018 - 05:47
Do you want it to look like that?
User 28/08/2018 - 05:47
right, but is there any way to make this populate automatically?
User 28/08/2018 - 05:47
yes that looks right
Excelchat Expert 28/08/2018 - 05:47
Yeah, it is possible.
User 28/08/2018 - 05:47
great!
Excelchat Expert 28/08/2018 - 05:48
Is the data in all the sheets the same references for each individual, just like you had put it in the July 13, 27 and Aug 10 sheets?
Excelchat Expert 28/08/2018 - 05:48
Or
Excelchat Expert 28/08/2018 - 05:48
Is it rampant?
User 28/08/2018 - 05:48
its the same
User 28/08/2018 - 05:49
they just copy and paste a template each time they make a new tab
Excelchat Expert 28/08/2018 - 05:49
Very good. Let me figure out how to formulate this.
User 28/08/2018 - 05:50
ahh actually i'm looking at it and it looks like its different, but i can go through and make it the same
User 28/08/2018 - 05:50
it seems like they added some people in the middle of the year
Excelchat Expert 28/08/2018 - 05:51
Okay.
User 28/08/2018 - 05:51
maybe just show me how to do it assuming all the tabs are the same
Excelchat Expert 28/08/2018 - 05:52
Okay.
Excelchat Expert 28/08/2018 - 05:52
Let me formulate the formula.
Excelchat Expert 28/08/2018 - 06:02
Almost done..
Excelchat Expert 28/08/2018 - 06:05
The formula that works is:
Excelchat Expert 28/08/2018 - 06:05
=SUM('July 13:Aug 10'!B2)
Excelchat Expert 28/08/2018 - 06:06
Summing Across multiple sheets:
[Uploaded an Excel file]
Excelchat Expert 28/08/2018 - 06:06
Check the file above.
Excelchat Expert 28/08/2018 - 06:08
I have no idea why it is not working on the online sheets, but it works on the desktop application.
Excelchat Expert 28/08/2018 - 06:09
Basically, its syntax is:
Excelchat Expert 28/08/2018 - 06:09
=SUM(‘FirstSheet:LastSheet’!A1)
User 28/08/2018 - 06:09
hmm ok i just see a lot of error messages. is it not working?
Excelchat Expert 28/08/2018 - 06:09
Replace FirstSheet and LastSheet with the names of the worksheets you wish to sum between. In this example, B2 would be the cell reference to sum.
Excelchat Expert 28/08/2018 - 06:10
Have you checked the file which I sent you?
Excelchat Expert 28/08/2018 - 06:11
Are you there?
Excelchat Expert 28/08/2018 - 06:12
Kindly respond, we have less than 4 minutes.
User 28/08/2018 - 06:12
yes, i see the document preview which has the error messages
User 28/08/2018 - 06:13
is there another place to see the document?
Excelchat Expert 28/08/2018 - 06:13
After downloading the file Enable it for editing.
User 28/08/2018 - 06:14
ok now it works! thanks!
Excelchat Expert 28/08/2018 - 06:14
There is an issue with the online sheets.
Excelchat Expert 28/08/2018 - 06:14
So basically the formula is:
Excelchat Expert 28/08/2018 - 06:14
=SUM('July 13:Aug 10'!B2)
Excelchat Expert 28/08/2018 - 06:15
Where:
Excelchat Expert 28/08/2018 - 06:15
=SUM(‘FirstSheet:LastSheet’!B2)
Excelchat Expert 28/08/2018 - 06:15
Replace FirstSheet and LastSheet with the names of the worksheets you wish to sum between. In this example, B2 would be the cell reference to sum.
Excelchat Expert 28/08/2018 - 06:16
It will sum all the corresponding cells for the range of the sheets given.
Excelchat Expert 28/08/2018 - 06:17
Is there any question you have in regards to this?
User 28/08/2018 - 06:17
i think that's it, thank you!
Excelchat Expert 28/08/2018 - 06:17
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
Excelchat Expert 28/08/2018 - 06:17
Please remember to rate us at the end of the chat for better and quality services. Thank you!

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.