If I understand what you're looking to do, you want D2 of each WS to total
certain cells in Column D of that sheet and then add the total from D2 of
the previous week's sheet, so that you have sort of a running total, from
week to week.
Let's start by adding the name of the each individual sheet to say A1 of
each sheet.
Group the already existing sheets so that what you do to one, will be done
to all, at the same time ... in a single shot.
Click on the tab of Week1, hold down <Shift>, and click in the tab of the
last sheet.
This turns all the tabs white, with the tab of Week1 also in bold, denoting
that it's the sheet in focus. You'll also see [Group] in the title bar,
next to the WB name, to remind you that you have sheets grouped.
Now, click in A1, and enter this formula:
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,25)
Now, ungroup the sheets by either (depending on version) right clicking in a
tab and choosing "Ungroup" or just clicking in any tab.
Go to each sheet and you should see that A1 contains the name of the
individual sheet.
Now, in Week1, let's say the formula in D2 is:
=SUM(D3
10)
In D2 of Week2, we want that same formula, *PLUS* the total from D2 of
Week1.
So, let's now *group* Week2 to the last existing weekly sheet, leaving Week1
*out* of the group.
With Week2 to whatever grouped, click in D2 and enter this formula:
=SUM(INDIRECT("Week"&RIGHT(A1,LEN(A1)-4)-1&"!D2"),D3
10)
This formula totals the Column D cells on the individual sheet, and then
looks at cell A1 for it's own name, so that it can go *back* one sheet to
get the total from that previous sheet's D2.
Don't forget to *ungroup* now.
You can copy these sheets when you need the next week, and the formulas
should duplicate and work automatically, after you rename the copied sheet.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
I am needing to track expenses for each week of the year. Column D will
total various Year-To-Date expenses from the previous week.
The Total YTD expenses for worksheet Week2!D2 would add the total
expenses on Week1!D2.
If I use =SUM(Week1:Week52!D2) I end up with the same value on every
worksheet. I do not want to include forthcoming weeks value.
