formulas

[Bruce]

How do you retain a value already in a cell and have it
update with the value entered in an adjacent cell? Example
I do a lot of Year To Date worksheets. If I had 5
whatevers in Jan 04 in column A1 my YTD date column B1
would say 5. Now I SAVE AS (the workbook) Feb 04 for the
next month. I remove the 5 in Column A1 but leave Column
B1 alone. It will still say 5. When I get the Feb 04 data
and let's say it is 10, I want the YTD data in Column B1
to update and say 15. One way I know is to have the
formulas refer back to the previous (Jan 04 YTD column)
but when you SAVE AS for the next month the formulas has
to be written again to refer to the previous monht. I do
100's of columns and was wondering if there is a simpler
way.........Thans Bruce

 

[Gord Dibben]

Bruce

You can have a cumulative total in a cell if you have a
separate source cell for adding a new total to the original.

Use at your own risk. I am Posting this just to show you how it can be done,
not as a good solution. You would be much better off to have another column so
you can keep track of past entries.

Goes like this: =IF(CELL("address")="$C$4",C4+D4,D4)

Enter this in cell D4 and then in Tools>Options>Calculation check Iterations
and set to 1.

Now when you change the number in C4, D4 will accumulate.

Note 1. If C4 is selected and a calculation takes place anywhere in the
Application D4 will update even if no new number is entered in C4. NOT GOOD.

Note 2. This operation is not recommended because you will have no "paper
trail" to follow. Any mistake in entering a new number in C4 cannot be
corrected. NOT GOOD.

To clear out the accumulated total in D4 and start over, select D4 and
Edit>Enter.

Check out Laurent Longre's MoreFunc.xla. Has a Function RECALL which does
what you want without the re-calculation problem, but again there is no "paper
trail" for back-checking in case of errors in data input.

http://longre.free.fr/english/func_cats.htm

Having said that, check out J.E. McGimpsey's site for VBA methods.

http://mcgimpsey.com/excel/accumulator.html

Gord Dibben Excel MVP