3D References not feasible for this problem

[BiancaSlate]

Excel Newbie here, and all I really want to do is get totals for how much money each person spent each month, on fuel and vehicle maintenance, and have it separated out by job paycode so that the correct departments get billed for it.

I have a workbook being used as a Vehicle Maintenance Logbook, with over twenty people, who each have their own separate tab, listing all of their vehicle-related expenses. The first page of the workbook is informational for HR, listing each person and their job paycode, and other bits of personal information I do not need to be worried about.

I've gone through the separate worksheet pages, trying to make them as similar as possible, to facilitate the use of functions throughout the workbook; however, I still cannot determine a way to get the monthly report I need, without going to each page and copying the data cell by cell, using the Consolidate Data function.
I know the individual pages are badly named for 3D use as they have spaces and special characters in them, but if I could get a way to produce the report easily, I'm certain I could talk The Powers That Be into letting me re-name them.
I need to produce a monthly report for each job paycode - there are eight. The worksheets are in alphabetical order and I am not allowed to group them by paycode for privacy's sake as well as HR likes them alphabetic - so after the Utility Vehicles, Bumblebury is first and Zathras is last (in my example).

The most people with the same paycode is eight, but none of them are close to each other, alphabetically. I've tried just biting the bullet and hand coding it myself, but I guess the worksheet names won't let me, with names like Utility #1 SR12948 and Wren, B SR12918.

Any helpful hints or suggestions would be welcome. I'm using Windows 7 and Excel 2010.

 

[Rukt]

Hi BiancaSlate – I must say that I don't envy you!

3D references work best (only?) with consecutive sheets. If Sheet1, Sheet2 and Sheet3 are in that order, =SUM(Sheet1:Sheet3!A1) is equivalent to Sheet1!A1+Sheet2!A1+Sheet3!A1. Note, however, that if the order is "Sheet1, Sheet3, Sheet2", then Sheet2!A1 will not be included in the sum (since it's "outside the range" Sheet1:Sheet3). With me so far?

The point is, I don't think there's much you can do if your sheets are in alphabetical order (short of doing everything by hand as you suggest), rather than some more logical order to facilitate reporting. At least grouping by pay code would be a start.

I realise that it would be a large outlay of initial effort, but I would strongly advise moving to a single sheet containing all information. This will allow you to summarise the data in any way you fancy using a PivotTable – this is exactly the sort of thing they're designed to handle. You've already taken steps in the right direction by trying to make the layout of each sheet identical; this is essential.

By your question, it sounds like there might be some privacy concerns with putting everything in one place. Perhaps you can elaborate a little, so we know what constraints we're working within? I'm not sold on "HR likes them alphabetic", either. Putting everything in one data table would allow them to sort alphabetically, filter to only show a given person or pay code, etc.

Anyway, let us know your thoughts on that path?

Cheers,
Rukt

 

[seatown1two]

Can you post a sample of your data and what you want the output to be?

 

[Aladin Akyurek]

@BiancaSlate

Have a look at:
http://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets.html

 

[BiancaSlate]

Thanks for the suggestions. I know you're operating half blind with what I can't say about the actual data on the sheets. When I work with them, I can rearrange them into PayCode order, but then when I'm done, I have to put them back in Alphabetical order. To do the reports, I have to hand-code using the individual sheet names (the tab names) to total up the fuel and maintenance cost cells on them for my report.

 

[BiancaSlate]

The MrExcel HTML Maker doesn't seem to be available any more. I used the URL from the Forum Posting Guidelines post.

Is there another place to get the Excel HTML maker? Thanks in advance!

 

[Aladin Akyurek]

Try to post (tiny amounts of) sample data from 2 sheets and show what the results you want to see, based on those 2 sheets.

 

[BiancaSlate]

Try to post (tiny amounts of) sample data from 2 sheets and show what the results you want to see, based on those 2 sheets.

Data from one sheet:

Month/YR

Mileage

Fuel $

Fuel Gallons

# days

Maint

Carwash

<tbody>
</tbody>

Apr-14	25	16	4	1	43	17
May-14	462	84	21	2

<tbody>
</tbody>

What I need:

May 31, 2014 Report	Fuel	Maint & Carwash
2014XXXYYYZZZXXXLICENSED	1036	157
2014XXXYYYZZZXXXSCREENDZZ	93	178
2014XXXYYYZZZXXXLAWGGGGG	1161	315
2014XXXYYYZZZXXXLAW26GGGG	289	392
2014XXXYYYZZZXXXCTRLCTR101	287
201XXXXYYYZZZXXXBATCHED999	136
2014XXXYYYZZZXXXBATCHED404	96	384
2014XXXYYYZZZXXXTONGENERIC	289

<tbody>
</tbody>

 

[Aladin Akyurek]

The longish ID's do not seem to exist in the data sheet. How come?

 

[Rukt]

Are those IDs the sheet names, BiancaSlate? Perhaps you could show us a made-up example where the values add up, across multiple sheets.