if..then formula..circular reference error

all,

I am getting circular reference error while using this formula...please suggest if anyone has way to resolve it...

thx in advance...

A2 =IF(A1="buy",(B1+A2),A2)

putting above formula in cell A2..

Originally Posted by martindwilson

yep !dont put it in a2 thats what a circular ref is you are refering to the cell the formula is in

but i want to add current value in A2 + B1 if A1 is BUY else keep the same value in A2...any idea..how to go ahead...

Originally Posted by Paul

A circular reference is when you reference the cell with the formula within the formula itself. In your case, you're referencing cell A2 in a formula stored in A2. This can be useful if you're trying to use iterations to increase a cell's value, but usually it's not necessary.

Can this function be stored in a different cell, say A3? If not, you would probably need to use a macro to update the value of A2 based on the value of A1 (in the Worksheet_Change event, perhaps).

I dont think so..we can store in A3 ...do i need to post this in excel programming section to get macro from someone? please suggest

You could of typed into anyother cell =IF(A1="buy",(B1+A2),A2) leaving out the "A2" before the "if".
Regards
Peter

Send a copy of the sheet if you like , remember no personal details.
Regards
Peter

Originally Posted by peterjuhnke

You could of typed into anyother cell =IF(A1="buy",(B1+A2),A2) leaving out the "A2" before the "if".
Regards
Peter

Thanks it works..i will try tomorrow morning again and update you..