Automatically update a cell if a condition is met

[awolfe]

I have a column (say column A) of values that gives the current values of stocks and two other columns (columns B and C) that give the highest and lowest values the stocks reach respectively. I want the B and C columns to update accordingly if column A rises above or drops below the values already in columns B and C.

For example:
A1 = $10, B1 = $15, C1 = $5

When A1 = $20, I want B1 = $20, C1 = $5
Then if A1 = $3, I want B1 = $20, C1 = $3

I've tried an IF statement but I end up doing a circular reference by trying something like IF(A1>B1,A1,B1)

Is there a way I can make the cells update accordingly without a circular reference error?

 

[VoG]

Try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
        If Target.Value > Target.Offset(0, 1).Value Then
            Target.Offset(0, 1).Value = Target.Value
        ElseIf Target.Value < Target.Offset(0, 2).Value Then
            Target.Offset(0, 2).Value = Target.Value
        End If
    Application.EnableEvents = True
End If
End Sub

then close the code window using the X.

 

[awolfe]

Try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
        If Target.Value > Target.Offset(0, 1).Value Then
            Target.Offset(0, 1).Value = Target.Value
        ElseIf Target.Value < Target.Offset(0, 2).Value Then
            Target.Offset(0, 2).Value = Target.Value
        End If
    Application.EnableEvents = True
End If
End Sub

then close the code window using the X.

That looks a little menacing, haha

I suppose I should have used the actual columns in my question, I actually used columns H, M, and O for the purposes I described, I just used A, B, and C for a simple example. I tried the code on a different page and it works like a charm, but now I wonder how I could adjust it to apply it to columns H, M, and O (current value, max value, and min value columns respectively)

Thanks!

EDIT:
I figured out how to adjust the code to fit any column I want

Thank you very much for your help, VoG II

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    Application.EnableEvents = False
        If Target.Value > Target.Offset(0, 5).Value Then
            Target.Offset(0, 5).Value = Target.Value
        ElseIf Target.Value < Target.Offset(0, 7).Value Then
            Target.Offset(0, 7).Value = Target.Value
        End If
    Application.EnableEvents = True
End If
End Sub

 

[awolfe]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 8 Then
    Application.EnableEvents = False
        If Target.Value > Target.Offset(0, 5).Value Then
            Target.Offset(0, 5).Value = Target.Value
        ElseIf Target.Value < Target.Offset(0, 7).Value Then
            Target.Offset(0, 7).Value = Target.Value
        End If
    Application.EnableEvents = True
End If
End Sub

Thanks a lot! Works like a charm

 

[ChristinaBB]

I normally do this through activating iterative calculations (File/Options/Formulas/Enable Iterative Calculation) and then write a formula as:
=IF(Condition which if true the cell should be updated,formula to update to,address of current cell) (i.e. if the condition is not true, the cell should be equal to itself - this is why iterations have to be allowed).

In your example, it should read:
In cell C1, type "=if(a1<c1,a1,c1)
In cell B1, type "=if(a1>b1,a1,b1)

 

[mochanita]

can i do the iteration with vlookup function? my objective is to leave the field empty (with no formula at all) whenever the referenced field is empty.

example:
column A is for product code and column B is product name.
i have a table in another sheet that lists down all the product code-product name.
whenever column A is not being entered with any code, i want column B to be totally blank (so, when it is being saved as .csv, it wont show anything, not even a comma).
but when column A is being filled in with any matching product code, i want column B to show the vlookup result.

thanks!

 

[ChristinaBB]

Try this formula in the B column

=if(isblank(A:A),"",vlookup(A:A,[range for lookup],[column],False)
This means there is always a formula in column B, but it displays as blank as long as the corresponding value in the A-column is blank.

 

[mochanita]

- since i'm trying to create a form here -- a form that will be saved in .csv format and inputted into a local app -- i will copy the formula to the whole column B.

- it's displaying the correct behavior. when i fill in A2 until A10, B2 until B10 is automatically filled in too.

- then i save the file as .csv.

- problem is, in the .csv file, row 11-.... is showing lots of commas ... -- while i need this to be empty/blank too, because the local app can't process a line that is only containing a comma.

- so .. the column B who seems blank is not actually blank, it contains formula that will appear in notepad.

-- any idea how to make this column B really really blank and empty if it's read using notepad (in .csv format)?

thanks!

 

[ChristinaBB]

sorry, I don't know. Maybe you need to write a macro, but then I am unfortunately not the person who can help you.