Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking info in cells
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking info in cells
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 On Thu, 14 Jun 2007 14:20:01 -0700, Yendorian wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locking info in cells
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 -- Gary''s Student - gsnu200729 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locking Cells | Excel Discussion (Misc queries) | |||
locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
Locking Cells | Excel Discussion (Misc queries) | |||
Locking Cells | Excel Worksheet Functions | |||
Link info in one cell to info in several cells in another column (like a database) | Excel Discussion (Misc queries) |