Totaling Cells in Worksheets

[Jim]

Need some help guys. I have a spreadsheet with data that
totals and then within the same worksheet I have a
summary of the totals. Example: I have a cell with the
formula =D34. I want to copy my data to a new worksheet,
change the values, then have that same cell =D34 + the
previous worksheets total without me having to type the
total from the previous sheet + D34.

Thanks Guys !!

JIm

 

[Ken Wright]

Not unless you're willing to go into code for an automatic solution. What
do the sheets represent, Days, weeks, Months, Years etc? Do you really need
them on separate sheets - Single sheet of data and a pivot table is often a
good solution for data analysis - all depends what you're doing though, so
give us some more detail.

 

[Jim]

Thanks Ken,
Here is the detail. I have employees hours for a week in
column D and total them at the bottom, D34. At the top of
the worksheet I have a summary of all the plants hours
I5, I6, etc. I want to copy the data to a new worksheet
for the new week and want the summary to carry over to
the new sheet, but carry the totals of the previous sheet
and add the new total for the current sheet without
changing the totals on the previous sheet. Clear as mud?

Thanks !!
Jim

 

[Ken Wright]

OK, I think I can now see what you are trying to do, but what cell on the
next sheet do you carry the value from D34 to?

Also, is every sheet a carbon copy of the last, ie same rows/columns etc

 

[Jim]

G3 equals the D34 total. Yes. Each sheet is a carbon copy
of the last.

Thanks Ken !!!

Jim

 

[Ken Wright]

Assuming you want 52 weeks, get the first week's sheet just right and make
it the last sheet in the book, and then run this.

Sub Week52()

Dim sc As Long
Dim last As Long
Dim x As Long
Dim y As Long
Dim z As Long

sc = ActiveWorkbook.Sheets.Count
last = sc
Sheets(last).Name = "Week 1"

For x = 2 To 52
Sheets(last).Copy After:=Sheets(last)
last = last + 1
Sheets(last).Name = "Week " & x
Next x

For y = 2 To 52
z = y + sc - 1
Sheets(z).Range("G3").FormulaR1C1 = "='Week " & (y - 1) & "'!R[31]C[-3]"
Next y

End Sub

 

[Jim]

Thanks Ken, but the total G3 does not add the current
sheets D34 to it?

Jim

-----Original Message-----
Assuming you want 52 weeks, get the first week's sheet just right and make
it the last sheet in the book, and then run this.

Sub Week52()

Dim sc As Long
Dim last As Long
Dim x As Long
Dim y As Long
Dim z As Long

sc = ActiveWorkbook.Sheets.Count
last = sc
Sheets(last).Name = "Week 1"

For x = 2 To 52
Sheets(last).Copy After:=Sheets(last)
last = last + 1
Sheets(last).Name = "Week " & x
Next x

For y = 2 To 52
z = y + sc - 1
Sheets(z).Range("G3").FormulaR1C1 = "='Week " & (y - 1) & "'!R[31]C[-3]"
Next y

End Sub



--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

--------------------------------------------------------- -------------------
It's easier to beg forgiveness than ask permission
--------------------------------------------------------- -------------------

G3 equals the D34 total. Yes. Each sheet is a carbon copy
of the last.

Thanks Ken !!!

Jim

-----Original Message-----
OK, I think I can now see what you are trying to do,

but
what cell on the

next sheet do you carry the value from D34 to?

Also, is every sheet a carbon copy of the last, ie

same
rows/columns etc MVP -
Excel

Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------------------------------

---
-------------------

It's easier to beg forgiveness than ask permission
------------------------------------------------------

---
-------------------

Thanks Ken,
Here is the detail. I have employees hours for a

week
in

column D and total them at the bottom, D34. At the

top
of

the worksheet I have a summary of all the plants hours
I5, I6, etc. I want to copy the data to a new worksheet
for the new week and want the summary to carry over to
the new sheet, but carry the totals of the previous sheet
and add the new total for the current sheet without
changing the totals on the previous sheet. Clear as mud?

Thanks !!
Jim
-----Original Message-----
Not unless you're willing to go into code for an
automatic solution. What
do the sheets represent, Days, weeks, Months, Years
etc? Do you really need
them on separate sheets - Single sheet of data and a
pivot table is often a
good solution for data analysis - all depends what
you're doing though, so
give us some more detail.

--
Regards
Ken....................... Microsoft MVP -
Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

---------------------------------------------------

---
--- ---
---

-------------------

Need some help guys. I have a spreadsheet with data
that
totals and then within the same worksheet I have a
summary of the totals. Example: I have a cell

with
the

formula =D34. I want to copy my data to a new
worksheet,
change the values, then have that same cell =D34

+
the

previous worksheets total without me having to

type
the

total from the previous sheet + D34.

Thanks Guys !!

JIm


.



.

.

 

[Ken Wright]

I may have misunderstood the brief. The code copies your Week 1 sheet which
initially is the last in the book, and creates a copy. It does this enough
times to give you 52 weeks. It then runs through and on each sheet from
sheet 'Week 2' on, in cell G3 it puts a link to the previous weeks cell D34,
so that 'Week 3' cell G3 has a link that says ='Week 2'!D34 and so on. Is
that not what was wanted?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

Thanks Ken, but the total G3 does not add the current
sheets D34 to it?

<sn5*