What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

A Pivot Table will do exactly what you want:

Sum of Hours by Job number.

see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200768


"rarch1" wrote:

What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

But a pivot table will not update the way the the OP want it to

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in message
...
A Pivot Table will do exactly what you want:

Sum of Hours by Job number.

see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200768


"rarch1" wrote:

What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different
column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to
the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

Hi Sandy

Whilst I agree that PT's do not update automatically, it is only a single
line of code set in a worksheet activate event to make it do so

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
End Sub

The name of the PT would need to be set to the name of the PT on the OP's
worksheet. This can be found by right clicking on the PTTable OptionsName

--
Regards
Roger Govier

"Sandy Mann" wrote in message
...
But a pivot table will not update the way the the OP want it to

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in
message ...
A Pivot Table will do exactly what you want:

Sum of Hours by Job number.

see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200768


"rarch1" wrote:

What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up
only
those cells that have all the same number (i.e. job no.)in a different
column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to
the
source, so if I change or clear the hours cell or the job no. cell in
the
source range, the totals reflect it.
Is there a way to do this? Please help.

Not only that but it looks like the OP has decided to go down the PT road as
well.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Sandy

Whilst I agree that PT's do not update automatically, it is only a single
line of code set in a worksheet activate event to make it do so

Private Sub Worksheet_Activate()
ActiveSheet.PivotTables("PivotTable1").PivotCache. Refresh
End Sub

The name of the PT would need to be set to the name of the PT on the OP's
worksheet. This can be found by right clicking on the PTTable
OptionsName

--
Regards
Roger Govier

"Sandy Mann" wrote in message
...
But a pivot table will not update the way the the OP want it to

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gary''s Student" wrote in
message ...
A Pivot Table will do exactly what you want:

Sum of Hours by Job number.

see:

http://peltiertech.com/Excel/Pivots/pivotstart.htm
--
Gary''s Student - gsnu200768


"rarch1" wrote:

What I would like to do is add up hours shown within specific columns
or
ranges on multiple timesheets. But here's the tricky part, to add up
only
those cells that have all the same number (i.e. job no.)in a different
column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked
to the
source, so if I change or clear the hours cell or the job no. cell in
the
source range, the totals reflect it.
Is there a way to do this? Please help.

Try:

=SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$B$2:$B$7 )

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"rarch1" wrote in message
...
What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different
column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to
the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

SUMIF works if I only want to reference a single source, but what if I need
to be linked to multiple worksheets or workbooks?

"Sandy Mann" wrote:

Try:

=SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$B$2:$B$7 )

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"rarch1" wrote in message
...
What I would like to do is add up hours shown within specific columns or
ranges on multiple timesheets. But here's the tricky part, to add up only
those cells that have all the same number (i.e. job no.)in a different
column
within that same row.
Example: One worksheet
Job Hours
601 8
602 16
601 4
704 11
408 6
602 7

Now to add up hours for each job no. on another totals worksheet:
Job Total Hours
408 6
601 12
602 23
704 11

While at the same time having the cells on the total worksheet linked to
the
source, so if I change or clear the hours cell or the job no. cell in the
source range, the totals reflect it.
Is there a way to do this? Please help.

Here are several methods that work across
multiple worksheets. Includes Pivot Table.
Excel 2003
http://www.freefilehosting.net/download/3bm61

Thanks for the help. It looks like I can do this really well with a pivot
table, but I'll have to work with it a bit and set up the source ranges a
little differently. But I also got the SUMIF formula to work also using
SUMIFS to add multiple criteria similar to fields in the pivot table and then
stringing the function together for multiple sheet references. Example:
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"408")+SUMIFS(Sh eet2!B2:B7,Sheet2!A2:A7,"408")+...and so on... for the hours for '408'.
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"601")+SUMIFS(Sh eet2!B2:B7,Sheet2!A2:A7,"601")+...and so on... for the hours for '601'.
This SUMIFS function method works for what I need right now, but it takes
lots of time inserting repetitive formulas for each field. I think the pivot
table will be more versatile - but I'll need to get more acquainted with all
the options first.
Thanks to everyone for your help on this!

rarch1

"Herbert Seidenberg" wrote:

Here are several methods that work across
multiple worksheets. Includes Pivot Table.
Excel 2003
http://www.freefilehosting.net/download/3bm61

Dang! Just take a look at Herbert's multiple methods and you can begin to
appreciate how powerful Pivot Tables really are!



--
RyGuy


"rarch1" wrote:

Thanks for the help. It looks like I can do this really well with a pivot
table, but I'll have to work with it a bit and set up the source ranges a
little differently. But I also got the SUMIF formula to work also using
SUMIFS to add multiple criteria similar to fields in the pivot table and then
stringing the function together for multiple sheet references. Example:
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"408")+SUMIFS(Sh eet2!B2:B7,Sheet2!A2:A7,"408")+...and so on... for the hours for '408'.
=SUMIFS(Sheet1!B2:B7,Sheet1!A2:A7,"601")+SUMIFS(Sh eet2!B2:B7,Sheet2!A2:A7,"601")+...and so on... for the hours for '601'.
This SUMIFS function method works for what I need right now, but it takes
lots of time inserting repetitive formulas for each field. I think the pivot
table will be more versatile - but I'll need to get more acquainted with all
the options first.
Thanks to everyone for your help on this!

rarch1

"Herbert Seidenberg" wrote:

Here are several methods that work across
multiple worksheets. Includes Pivot Table.
Excel 2003
http://www.freefilehosting.net/download/3bm61