Circular Reference Conundrum
 

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

Circular Reference Conundrum
 

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

--
Regards
Frank Kabel
Frankfurt, Germany


Tom wrote:
Hi All,

Here is my puzzle.

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

= 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" Then

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

If intfsvj2 intsemij2 Then
intruncount = intfsvj2 + 1
Else
intruncount = intsemij2 + 1
'End If

If intfsv = 1 Then
Sheets("4 Cavity FSV Set-Up Sheet").Activate
Range("V3") = intruncount
Else
Sheets("4 Cavity Set-Up Sheet").Activate
Range("X6") = intruncount
'End If
End If
End If
End If

I appreciate any help that may be provided

Tom