How to change the value of a cell when a condition is satisfied, but not otherwise?
 

I have a small problem.
My requirement is as follows:

I have two cells and have enabled manual calculation. The first cell
loops through the values 1-5 whenever F9 key is pressed.
The function for this is
-*B9*=IF(B9="1","2",IF(B9="2", "3", IF(B9="3", "4", IF(B9="4", "5",
"1"))))-
Whenever the value of cell B9 changes from 5 to 1, I need the value of
cell C3 to change, but never otherwise.
The complication is that cell C3 changes randomly every time B9 changes
from value 5 to value 1.
Right now, my formula for C3 is
-*C3*=IF(B9="5",Rand()*100,C3)-
Yes, I know that a circular reference is created, so I used manual
calculation and made iteration limit to 1, change limit to 0 in
ToolsOptionsCalculation.
It seems to work, but I need a better solution, because I cannot refer
cell C3 from any other formula as it says it causes a circular
reference. I don't know why the circular reference in C3 is not caught
in the first place.

Can anybody help me with this issue?


--
benzi_k_ahamed
------------------------------------------------------------------------
benzi_k_ahamed's Profile: http://www.excelforum.com/member.php...o&userid=30352
View this thread: http://www.excelforum.com/showthread...hreadid=500093