I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson

Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new sheet
via the duplication of the previous sheet but this may have to be deleted due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a sample
of the workbook to your personal address if needed. Thank you for your time!

"Dave Peterson" wrote:

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson

I would use a macro--so that won't work for you.

You may want to post in the .worksheet.functions newsgroup. Maybe there's some
giant formulas you could use (way beyond me!).

MGC wrote:

Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new sheet
via the duplication of the previous sheet but this may have to be deleted due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a sample
of the workbook to your personal address if needed. Thank you for your time!

"Dave Peterson" wrote:

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson


--

Dave Peterson

Dave/Roger:

Sorry for the lateness of my reply. If I wanted to run this with my current
macro, could I? and what would I need to do to be able to do this? The
following is the macro currently in use:

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy at the 'end' of the workbook

Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)
'remains on the new sheet
End Sub

How do you convince your company that the macro doesn't contain a
virus...that's the reason I may need to stop using it. Thanks for all the
help you guys!!!

"Dave Peterson" wrote:

I would use a macro--so that won't work for you.

You may want to post in the .worksheet.functions newsgroup. Maybe there's some
giant formulas you could use (way beyond me!).

MGC wrote:

Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new sheet
via the duplication of the previous sheet but this may have to be deleted due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a sample
of the workbook to your personal address if needed. Thank you for your time!

"Dave Peterson" wrote:

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson


--

Dave Peterson

Most of the original thread has aged off for me. But there's nothing in your
code that precludes running that on any "normal" macro (that I can see, anyway).

But I'd use:
ActiveSheet.Copy after:=Sheets(sheets.Count)
instead of:
Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)



MGC wrote:

Dave/Roger:

Sorry for the lateness of my reply. If I wanted to run this with my current
macro, could I? and what would I need to do to be able to do this? The
following is the macro currently in use:

Sub DuplicateCurrentSheet()
'
'Copies the currently selected sheet
'placing the copy at the 'end' of the workbook

Worksheets(ActiveSheet.Name).Copy after:=Sheets(Worksheets.Count)
'remains on the new sheet
End Sub

How do you convince your company that the macro doesn't contain a
virus...that's the reason I may need to stop using it. Thanks for all the
help you guys!!!

"Dave Peterson" wrote:

I would use a macro--so that won't work for you.

You may want to post in the .worksheet.functions newsgroup. Maybe there's some
giant formulas you could use (way beyond me!).

MGC wrote:

Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new sheet
via the duplication of the previous sheet but this may have to be deleted due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a sample
of the workbook to your personal address if needed. Thank you for your time!

"Dave Peterson" wrote:

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex. B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi

Pivot tables would work, there are just more fields to select than you led
me to believe in your previous posting.

Anyway, another alternative might be as follows.
Ensure that Order number is the first column on all of your sheets.
If they are not, then select first sheethold down ShiftSelect last sheet
and this will Group the sheets.
Select the column with Order NumberCutSelect column AInsert Cut cells
Select any individual sheet tab to Ungroup the selection

On a blank sheet that is to be your Summary sheet, choose DataConsolidate
Leave the first dialogue box as Sum.
Click on your first sheet, select all the columns that you needclick
Addmove to second sheet, the range will later to reflect the new sheet
nameClick Addrepeat until all sheets required have been selected.
Click Use Label in Top row and Left ColumnOK

This will give you Totals of Hours for each Order number
Whenever you need to refresh, just go to this new Summary sheet and Choose
DataConsolidateOK as all the ranges will have been remembered.
--

Regards
Roger Govier

"MGC" wrote in message
...
Dave:

Along these same lines: I have several worksheets which I need to combine
into a summary page on the first tab. The workbook contans several
worksheets (one for each day a job is worked) with the hours for each
person.
One sheet (day) may contain data for up to 15 guys. I need to be able to
extract the date (B6) , class (GF, F, JW, AP) (C9:C44) and the number of
hours of straight time, overtime and double time (the letters ST, OT and
DT
are in column E but the actual hours are in column F) for each individual
class onto the summary page.

The goal here is to be able to track how much I have remaining on a
purchase
order. My timesheets cannot be modified, a pivot table does not provide a
way that I can find to do this easily and macros are frowned upon. I
currently have a macro in use on this workbook that will create a new
sheet
via the duplication of the previous sheet but this may have to be deleted
due
to the company not liking macros created by an outside source.

Can my dilemma be solved? If I have you confused, I could send you a
sample
of the workbook to your personal address if needed. Thank you for your
time!

"Dave Peterson" wrote:

Create two new worksheets--one to the far right and one to the far left.

Call them Start and End

Then using a sheet (Summary) that is outside this "sandwich" of
worksheets:

=sum(start:end!B37)

Then you can drag sheets in and out of that sandwich to play what if
games.

I'd put a couple of notes on each of these sheets:

"don't delete this sheet!"

And protect the worksheet so that people don't use it for real data.

ACM wrote:

I have a workbook with 12 worksheets and I need to sum 1 cell (ex.
B37) on
each worksheet on one summary page. How can I track the cell from the
different worksheets to add them?

--

Dave Peterson