Circular Reference Conundrum

[Guest]

Hi All

Here is my puzzle

I have the following formula in cell BA20 on to seperate page

= if(C4="A1",V3,BA20

this creates the circular reference

the value in the cell could be anything between 1 to 10,000

in code I look to see which one is the larger of the two and add 1 to it ,which in turn gets placed into cell V3

What I am looking for is how can I leave a cells value alone until a certain criteria happens,
like Cell C4="A1" with out getting a circular reference

Below is a copy of the code I am using to look to see which value is greater then add 1 to it

If strce = "J2" The

Sheets("4 Cavity FSV Set-Up Sheet").Activat
intfsvj2 = Range("BG9"
Sheets("4 Cavity Set-Up Sheet").Activat
intsemij2 = Range("BL20"

If intfsvj2 > intsemij2 The
intruncount = intfsvj2 +
Els
intruncount = intsemij2 +
'End I

If intfsv = 1 The
Sheets("4 Cavity FSV Set-Up Sheet").Activat
Range("V3") = intruncoun
Els
Sheets("4 Cavity Set-Up Sheet").Activat
Range("X6") = intruncoun
'End I
End I
End I
End I

I appreciate any help that may be provide

Tom

 

[Frank Kabel]

Hi
if I understood you correctly you'll need an event procedure for this
(e.g. use the worksheet_change event). see
http://www.cpearson.com/excel/events.htm