Data Consolidation

[Guest]

I have 20+ worksheets in a workbook. Each sheet is a timesheet for an
individual and each sheet can have up to eight cost codes which will be the
same on some sheets but will not be in the same position on all sheets. How
do I label or set up a range so I can consolidate all of these cost codes on
a seperate sheet to give me the total hours for each cost code?

 

[Guest]

Could you for example do the following:
Set up your new sheet with a table with all the cost codes in a column on
the left, and all the people at the top. Then put a sumif, or sumproduct
formulae so that it adds up all the hours for a particular person and a
particular cost code in one cell in the table.

e.g. suppose that there are sheets called Person1 and Person2, with cost
codes in A4:A11, and hours in B4:B11.

Then in your new sheet you list your cost codes for example in A4:A20. In
B3 and C3 you have Person1 and Person2. Then in B4 you have the formula
=SUMIF('Person1'!$A$4:$A$11,$A4,'Person1'$B$4:$B$11)

You can drag this down, and it will populate your hours table for Person 1.
Dragging across, it unfortunately won't update to the people automatically.
It's a bit of a pain in the butt, but for ~20 sheets it's probably quickest
just to change the references in each column to Person2, etc. Then you can
easily drag them across.

It is possible to have the references update according to what you've got in
the top of the column, using INDIRECT, but it's frankly easier to do it
manually in this case, I think.

 

[Guest]

The way these weekly timesheets are set up are eight columns across the top
for cost codes with 2 rows for each day of the week (one regular time and one
overtime) under each cost code. Unless I do a tedious manual 3D
consolidation, I can't see a way to pull these numbers together. I'm hoping
for an automated consolidation using labels or ranges and Data:
Consolidation. Or should I be looking at a database?

 

[Guest]

What format do you want your summary page to have? Should it show breakdown
by person, day, overtime/normaltime? Are there only 8 codes and they're
mixed up or are there more than 8 codes?

Regards

 

[Andy Wiggins]

This file might be a help:
http://www.bygsoftware.com/examples/zipfiles/consol.zip
It's in the "Accountants" section on page:
http://www.bygsoftware.com/examples/examples.htm

The "Bread-Roll" consolidation method - great for accountants. See how
simple it is to consolidate any combination of your organisation's accounts.
(No VBA used).

The same principle can be applied to your timesheet problem.

 

[Guest]

I'd like to see the total hours for each cost code for the week (no breakdown
by person or day or regular/overtime). I have a sample timesheet I could
attach if I knew how/where, if that would help. There are only 8 spaces
availabe for different cost codes on each timesheet but the total available
cost codes is ~50.

 

[Guest]

So although the "bread roll" method is very cool, I'm not sure it answers
your question.

This is the situation as I understand it.
You have 20 timesheet sheets, with
- 8 spaces for cost codes (let's say B5:I5)
- 14 rows of hour data (2 for each day of the week), let's say B6:I19.

You want a consolidation sheet with
- 50 rows of data consisting of the 50 cost codes and the total of all the
hours in all the columns of all the sheets (let's say A6:A55 has the cost
costs, and B6:B55 has the hours).

This is a little tricky.
Option 1:
In your consolidation sheet you can do a formula like this in say C6 and
drag it down to C55:
{=SUM(IF('Person 1'!$B$5:$I$5=$A6,'Person 1'!$B$6:$I$19))}

**Please note this is an array formula. Type it without the braces, and
then press SHIFT+CTRL+ENTER**

You then do similar formulae going in columns D,E,F etc with all the 20
people (i.e. drag it across and then edit Person 1 to Person 2 etc.) Then you
make the B column equal to the sum of all the columns to it's right. This is
essentially giving you the breakdown per person and then totalling it.

This method would be my recommendation.
Method 2:
If you're set on getting it all in one cell, then, I think, you'd have to
write a macro, which you'd have to run everytime you wanted to look at the
data. It wouldn't be terribly difficult. If you want to go that route, then
please write back.

Hope this helps.

 

[Guest]

After looking at your suggestions and the "bread-roll" consolidation, I
modified my timesheets so the cost codes and hours are together off to the
side and named each of those areas as a range and then inserted a worksheet
on which I used Data: Consolidation and it's working. The only problem is
updating. If I need to add a cost code to one of the timesheets after I have
already performed a consolidation, it doesn't automatically update to add
that cost code and it's corresponding hours. If I use the consolidation
command again on the same page, things get screwy. Is there a way to
"refresh" a data consolidation? If I simply add or subtract hours from an
existing cost code, then the number automatically update.