Archive

[John Hunt]

If I have two cells say A1 and B1 and I always use these cells to input my
data. How can I archive this information to another cell. For example: If
A1 = 5 and B1 = 6 and I want this information inserted in C1 and D1. The
next time A1 = 10 and B1 = 11. I want these values inserted into cells C2
and D2 and so on. So what ever I insert into cells A1 and B1 it contantly
archives the information into cells C1 and D1 and down. Please help!!!

 

[Frank Kabel]

Hi
this is not possible using formulas alone. This would require VBA

 

[John Hunt]

That is what I was afraid of. Thanks for the reassurance though.

 

[Gord Dibben]

John

If you want the VBA, try this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" _
And IsNumeric(Target.Value) Then
ActiveSheet.Cells(Rows.Count, 3).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
If Target.Address = "$B$1" And Target.Value <> "" _
And IsNumeric(Target.Value) Then
ActiveSheet.Cells(Rows.Count, 4).End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
stoppit:
Application.EnableEvents = True
End Sub


NOTE: it will place first two numbers from A1 and B1 into C2 and D2

Just delete C1 and D1(shift cells up) and then you're good to go from then on.

Right-click on your sheet tab and select "View Code". Copy the above code
into the module that opens.

Gord Dibben Excel MVP

 

[Hammer]

Thank You very much. It works!!! This exactly what I was after.

Thanks!
John