circular reference (Excel 2000)

P

peaspud

Hi, I need help.I'm sure there is a simple answer,but i can't seem to find
it??!! All i'm trying to do is this:
A1 A8
25 25
Simple? I need the total (A8) to add the input number (A1), even if i change
the input number.But all i seem to get is a "circular reference"
comment.There must be a formula out there that can help me??
 
I

Ian

Do you mean you need A8 to equal A1 (as your example suggests)? In A8 enter
=A1 to do this.
Do you mean you need a add A1 to whatever is already in A8? In A8 enter
=A1+A8 to do this. This will result in a circular reference. Go to
Tools>Options>Calculation, select Iteration and change Maximum iterations to
1. The problem with this is that every time the sheet is recalculated, A1
will again be added to A8. There are ways round this using code, but there's
no point in going into this unless this is really what you need.
 
P

peaspud

Thank you for your reply.However it did'nt seem to help me, not your fault.I
think i need to explain it better.I need A8 to add together each input number
i put in A1.Even if i delete the A1 number to put in another, i need A8 to
give me a total of each number i've put in and deleted.Does that make
sense???...Hope you can help i'ts driving me mad!!
 
B

Bob Phillips

Could you possibly mean just putting =A1 in A8?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
V

vezerid

peaspud said:
Hi, I need help.I'm sure there is a simple answer,but i can't seem to
find
it??!! All i'm trying to do is this:
A1 A8
25 25
Simple? I need the total (A8) to add the input number (A1), even if i
change
the input number.But all i seem to get is a "circular reference"
comment.There must be a formula out there that can help me??
Click to expand...

For your re-reply I understand that you want A8 to contain the
cumulative sum of all numbers entered in A1. This can be done in the
following manner:

1. Use another (say K1) which will contain the number 0 or 1. This will
be used to initialize your calculations. Initially enter 0.

2. Go to Tools|Options|Calculation tab and check Iterations and set
Maximum Iterations to 1.

3. In A8 put the formula =IF(K1=0, 0, A8+A1)

This last formula will initially show 0. You can enter your first
number in A1. Then you set K1 to 1. As long as K1 is 1, evey number you
enter in A1 will be added to A8.

HOWEVER!: This addition will take place every time you recalculate for
any reason. Even if you enter a number in another cell this will cause
recalculation. And if so, the current number in A1 will be re-added.

HTH
Kostis Vezerides
 
P

peaspud

Thank you all for your help.It seems to have done the trick.Problem is ,now i
have the answer, im not sure if i was doing it right in the first place.??
I shall try out the formula i have now and see if it works ok for what i
wish to do,
if it does not work i hope i can ask for all your help again???
Thanks once again
 
Top