Locking info in cells

[Guest]

In Excel Pro 2003, I am trying to enter data from one cell only (A1 – Jones)
and have it saved in another column of cells.(Column C1). If I enter ‘=$A$1’
in cell C1, I get the name in that cell – which is fine.
My problem is that I now want to enter a new name from cell (A1 – this time
Brown) and have it saved in the new cell C2 without changing the Jones name
in C1, but of course the formula ‘=$A$1’ puts Brown in the new cell. C2 and
changes
C1 to Brown also.
Ideally I want to enter Jones, Brown, Smith, Evans, Davis etc. into cell A1
and have them fixed iin Column C giving a list of different names in Column
C. Any help gratefully received.

 

[Gord Dibben]

Why not just enter the names in column C and dispense with A1 and linking?

But here is a method using event code.

First in C1 copy/paste as value the formula resulting in Jones.

Then right-click on the sheet tab and "View Code".

Copy/paste this event code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
With Target
If .Value <> "" Then
ActiveSheet.Cells(Rows.Count, "C").End(xlUp) _
.Offset(1, 0).Value = Target.Value
End If
End With
stoppit:
Application.EnableEvents = True
End Sub

As you enter names in A1 they will be listed down column C


Gord Dibben MS Excel MVP

 

[Guest]

Use this small event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
Application.EnableEvents = False
n = Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(n, "C").Value = Target.Value
Application.EnableEvents = True
End Sub

 

[Don Guillett]

right click sheet tab>view code>copy/paste this. Now when you put anything
in cell a1 it will be added to a list starting at the next available row in
col C. Why Jones? You may want to delete the Target="Jones" line.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address <> "$A$1" Then Exit Sub
lr = Cells(Rows.Count, "c").End(xlUp).Row + 1
Cells(lr, "c") = Target
Target = "Jones"
Application.EnableEvents = True
End Sub