I would like to find a way to combine monthly gas usage by employee data into one summary spreadsheet....
I would like to find a way to combine monthly gas usage by employee data into one summary spreadsheet....
Last edited by SteveHarley; 07-29-2015 at 11:46 AM. Reason: i messed up..should have attached a spreadsheet
Sorry, it is a bit confusing, could you attach a file? If not, could you try and explain 1- what do you have and 2- what are you looking for?
Is it that you have a spreadsheet with example:
Bill 50$
Bill 30$
Steve 20$
Steve 30$
and want:
Bill 80$
Steve 50$
If not, I don't really understand your goal
Individual Individual
Spreadsheet Spreadsheet Summary Spreadsheet
May 2015 June 2015
Employee May 2015 June 2015
Bill $50 $25 Bill $50 $25
Joe $25 Joe $25
Amber $29 $25 Amber $29 $25
Sue $12 Sue $12
John $36 $25 John $36 $25
Steve $18 $70 Steve $18 $70
Last edited by SteveHarley; 07-29-2015 at 11:56 AM. Reason: ok....I obviously need the beginner course in uploading attachments...sorry.
This is kinda hard to understand still. Could you simply explain it instead? Formatting it like this in a post is almost impossible to understand, please try to describe what you're trying to do.
Attached is a file which shows what I'm trying to accomplish...
Any help is appreciated.
Thanks!
It depends, in your individual spreadsheets, is only the total amount written? Or could there be multiple instance of the same name?
Example, in spreadsheet may 2015, could you see Bill 3 times and have to sum the 3 amounts?
I put an example with both cases there:
Yep...just the total amount is reflected on the individual spreadsheets (I can do a pivot and summarize by name)...my challenge is how to get by name by month totals into the summary spreadsheet without a lot of manual intervention/...
Thanks!
It appears that you are having your data horizontally organized but it is really not at all clear what you want in a summary worksheet as the summary area appears to be exactly the same as the original. There has to be something here that you are not presenting otherwise what is the point.
To sum the data in the individual spreadsheet and put the total in the summary spreadsheet this is what I used in the example:
Enter in F5 and fill down and across:
Formula:Please Login or Register to view this content.
A B C D E F G 1Summary Spreadsheet 2Individual Individual Employee 3Spreadsheet Spreadsheet 4 May-15 Jun-15 May-15 Jun-15 5Bill $50 $25Bill $50 $25 6Joe $25Joe $25 $0 7Amber $29 $25Amber $29 $25 8Sue $12Sue $12 $0 9John $36 $25John $36 $25 10Steve $18 $70Steve $18 $70
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
You are correct...I haven't included information noting that the individual monthly spreadsheets are different each month....some employees may not use gas for a particular month, new employees are added, etc...attached is a spreadsheet that hopefully explains the scenario...
thanks for your patience!
In B7:
=IFERROR(VLOOKUP($A7,INDIRECT(B$6&"!A:B"),2,FALSE),0)
Then drag it for every person / month.
For the last column, just use sum()
Thank you Jdevil...going to have to put you on the payroll for all your help (and patience)
Thanks again...
Take a look at this.
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Here is one way to calculate for each employee. Enter this in B7 and fill across and down. All values for each employee for each month is brought forward to the summary. The totals are calculated with SUM at the bottom of each column and end of each row.
Formula:Please Login or Register to view this content.
As you add new worksheets, enter the tab name in the appropriate column of the summary and fill the formula across from the other cells.
Here is your workbook with a couple of months added. All I had to do was to add the month names to the summary (matching the tabs of the new worksheets) and copied the names into A2:A8 of the new worksheets. As you add values to these new worksheets the values will be brought forward to the summary.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks