Formula retaining last value of cell ?

[snownut2]

I am trying to use an "IF" statement to either apply a formula or retain the last value of the cell, is there a way to accomplish this.

eg. = IF( A1=B1, "retain last value", D1)

last value = the value of the cell prior either to A1 or B1 being changed.


Thanks for any assistance.

Bruce

 

[snownut2]

I've tried this;

=IF (A1<>B1, D1)

it returns "FALSE" when A1 & B1 are equal.

Is there a way to leave the Cell as it was if A1 & B1 are equal ?
ie; do nothing in that case.

 

[Marcelo Branco]

Hi,

You cannot achieve this with a formula, only with VBA

Maybe this

Right-click on the sheet-tab
View code
and paste the code below in the right-panel

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
        If Range("A1").Value <> Range("B1").Value Then
            Range("C1").Value = Range("D1").Value
        End If
    End If

End Sub

HTH

M.

 

[snownut2]

Marcelo thank you very much your code worked perfectly, however i am really trying to have that apply to every row in the worksheet, not just row 4.

I know that is exactly what I had asked for, as I thought a formula would work I could have just copied it the the rest of the column.


Thanks again

Bruce

 

[Marcelo Branco]

Marcelo thank you very much your code worked perfectly, however i am really trying to have that apply to every row in the worksheet, not just row 4.

I know that is exactly what I had asked for, as I thought a formula would work I could have just copied it the the rest of the column.


Thanks again

Bruce

Maybe...

Private Sub Worksheet_Change(ByVal Target As Range)
 
    If Not Intersect(Target, Range("A:B")) Is Nothing Then
        If Range("A" & Target.Row).Value <> Range("B" & Target.Row).Value Then
            Range("C" & Target.Row).Value = Range("D" & Target.Row).Value
        End If
    End If
 
End Sub

M.

 

[snownut2]

Again Thank you Marcelo, that is perfect.


Bruce

 

[Marcelo Branco]

Again Thank you Marcelo, that is perfect.


Bruce

Bruce,

You are welcome and tks for the feedback

M.