keeping a running tally w/o causing a circular reference

[rod_inman]

i am trying to do something that i think is simple. but im am getting
circular reference erro.. maybe some one can point me to a differen
way to do what i want to achieve.

in the attached xls sheet i want D8 to keep a running tally of R8, her
is where i get bewildered, i would like R8 to be able to be reset eac
week and yet D8 still retai a running total of prior weeks. each week
reset the data in cells F H J L N P. in my feeble mind this seems eas
to do. .but as the story goes easier said than done.. so, if anyone ca
assist me i would be happier than pig in a wollar. im open for an
ideas even if it means redoing the sheet completely. .than

Attachment filename: my work.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=62474

 

[Jerry W. Lewis]

A cell formula does not have memory. The only way to build in memory is
with VBA (You chould have a worksheet change event that updates D8 when
there is a change in R8).

As a practical matter, memory in a calculation is almost always a bad
idea. How would you deal with data entry errors in R8? If the running
total somehow gets out of sync, how would you correct, or even detect it?

Jerry

 

[Dave Peterson]

I would take Jerry's advice very seriously--but if you really must, J.E.
McGimpsey has some code you could steal/modify at:

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Be careful.

 

[Gord Dibben]

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, Dave's pointer to John's website is your next bet.

Gord Dibben Excel MVP

 

[rod_inman]

a thousand thank yous for the replys. i will look into the advice give
me. thanx agai