Moving a cell value to another cell using formula.

[Roger S]

Question please:-

If i have a number value in cell A1 and by puting an "X" in cell A2, the number value will move to cell A3 automatically, leaving cell A1 blank.

 

[James006]

Hi,

A formula cannot fully handle your request ...
You could test following event macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row Mod 2 = 1 Then Exit Sub
    With Target
        If UCase(.Value) = "X" Then
            .Offset(1, 0).Value = .Offset(-1, 0).Value
            .Offset(-1, 0).ClearContents
        End If
    End With
End Sub

 

[Roger S]

Hi,

A formula cannot fully handle your request ...
You could test following event macro

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Row Mod 2 = 1 Then Exit Sub
    With Target
        If UCase(.Value) = "X" Then
            .Offset(1, 0).Value = .Offset(-1, 0).Value
            .Offset(-1, 0).ClearContents
        End If
    End With
End Sub

Thanks James, i will give it a try.

One question please, there are other formuli in the same sheets, will the be effected by adding this code?

 

[James006]

Three quick remarks :
1. Macro can very easily be restricted to a predefined area
2. As a general rule rule, there should be no interference ... but ...
3. What precisely are the formulas you are talking about and where are they located ?