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.
