Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Running Total for each worksheet
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
New Running Total for each worksheet
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:D10) 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:D10) 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! ================================================== === "Jeff" wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running total that stays with changes | Excel Worksheet Functions | |||
Running Total from previous page | Excel Worksheet Functions | |||
RUNNING TOTAL | Excel Worksheet Functions | |||
total a range of cells in Excel into a different worksheet | Excel Worksheet Functions | |||
How do i get the running balance from one worksheet to another? | Excel Worksheet Functions |