|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?
|
|
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.
--
Glenton
www.leviqqio.com
Quality financial modelling
"NMCyd" wrote:
[Quoted Text] > 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?
|
|
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?
"glenton" wrote:
[Quoted Text] > 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.
> --
> Glenton
> www.leviqqio.com
> Quality financial modelling
>
>
> "NMCyd" wrote:
>
> > 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?
|
|
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
--
Glenton
www.leviqqio.com
Quality financial modelling
"NMCyd" wrote:
[Quoted Text] > 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?
>
> "glenton" wrote:
>
> > 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.
> > --
> > Glenton
> > www.leviqqio.com
> > Quality financial modelling
> >
> >
> > "NMCyd" wrote:
> >
> > > 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?
|
|
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.
--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-
"NMCyd" <NMCyd[ at ]discussions.microsoft.com> wrote in message
news:605EA88B-453B-469E-98BB-93A8922E9741[ at ]microsoft.com...
[Quoted Text] > 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?
>
> "glenton" wrote:
>
>> 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.
>> --
>> Glenton
>> www.leviqqio.com
>> Quality financial modelling
>>
>>
>> "NMCyd" wrote:
>>
>> > 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?
|
|
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.
"glenton" wrote:
[Quoted Text] > 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
> --
> Glenton
> www.leviqqio.com
> Quality financial modelling
>
>
> "NMCyd" wrote:
>
> > 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?
> >
> > "glenton" wrote:
> >
> > > 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.
> > > --
> > > Glenton
> > > www.leviqqio.com
> > > Quality financial modelling
> > >
> > >
> > > "NMCyd" wrote:
> > >
> > > > 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?
|
|
|