Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total that stays with changes
I need help being able to have a running total column on 1 worksheet, while
using other worksheets with this total. Then be able to change the input data and the running totals stay as they were changed from the last entries and ready to change with the new entries. Any thoughts? Jim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total that stays with changes
Jim,
you can use intentional circular reference for this (Tools|Options|Calculation tab, check the Iteration option). I assume that your target formulas are of the form =[C:\Book]Sheet!A1 You can have a flag cell, next to your source data, containing 0 or 1. While it is 0, the target cells will keep their value, even if the source cells change. When it becomes 1 the target cells will be updated with the current value. If you set the flag back to 1 you "lock" target again. Assuming flag is in [C:\Book]Sheet!K1, use these formulas in the target, reflecting cells: =IF([C:\Book]Sheet!K1=0,A1,[C:\Book]Sheet!A1) This formula is placed in cell target workbook, target sheet, target cell A1 (which appears in the 2nd argument, hence the CR) and is reading the corresponding cell of [C:\Book]Sheet A similar discussion appears in a post of yesterday, with some additional considerations concerning consistency of the calculation environment. http://tinyurl.com/qe2s4 HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Running total that stays with changes
Kostis, this sounds like it will work in some instances, but with our
situation, we will have multiple parts for each cabinet and we would have to set a flag cell for each one and it sounds like we'd have to each time go through and change the 0s and 1s and there is too many to do this. We need a similar function to be able to use and not have to go back each time to manually change a value, there has to be a way to set this up automatically. Do you know of any other way to do this and have it change when it changes and just keep the new value every time? Thanks, Jim "vezerid" wrote: Jim, you can use intentional circular reference for this (Tools|Options|Calculation tab, check the Iteration option). I assume that your target formulas are of the form =[C:\Book]Sheet!A1 You can have a flag cell, next to your source data, containing 0 or 1. While it is 0, the target cells will keep their value, even if the source cells change. When it becomes 1 the target cells will be updated with the current value. If you set the flag back to 1 you "lock" target again. Assuming flag is in [C:\Book]Sheet!K1, use these formulas in the target, reflecting cells: =IF([C:\Book]Sheet!K1=0,A1,[C:\Book]Sheet!A1) This formula is placed in cell target workbook, target sheet, target cell A1 (which appears in the 2nd argument, hence the CR) and is reading the corresponding cell of [C:\Book]Sheet A similar discussion appears in a post of yesterday, with some additional considerations concerning consistency of the calculation environment. http://tinyurl.com/qe2s4 HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table for reporting sales performance | Excel Discussion (Misc queries) | |||
Running total at bottom of column? | Excel Discussion (Misc queries) | |||
Running total w/2 columns - Excel | Excel Worksheet Functions | |||
running total for last 7 calender days | Excel Discussion (Misc queries) | |||
how do I keep a running total of my bills? | Charts and Charting in Excel |