combine monthly gas usage by employee

[SteveHarley]

I would like to find a way to combine monthly gas usage by employee data into one summary spreadsheet....

[Jdevil]

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

[SteveHarley]

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

[Jdevil]

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.

[SteveHarley]

Attached is a file which shows what I'm trying to accomplish...
Any help is appreciated.
Thanks!

[Jdevil]

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:

[SteveHarley]

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!

[newdoverman]

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:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G
1						Summary	Spreadsheet
2		Individual	Individual		Employee
3		Spreadsheet	Spreadsheet
4		May-15	Jun-15			May-15	Jun-15
5	Bill	$50	$25		Bill	$50	$25
6	Joe	$25			Joe	$25	$0
7	Amber	$29	$25		Amber	$29	$25
8	Sue	$12			Sue	$12	$0
9	John	$36	$25		John	$36	$25
10	Steve	$18	$70		Steve	$18	$70

[SteveHarley]

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!

[Jdevil]

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()

[SteveHarley]

Thank you Jdevil...going to have to put you on the payroll for all your help (and patience)
Thanks again...

[popipipo]

Take a look at this.

[newdoverman]

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.

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.