MrExcel Publishing
Your One Stop for Excel Tips & Solutions

creating a list from repeated one cell entry


Posted by Donald on December 06, 2001 12:39 AM

I want to enter information into one cell in sheet1 but want a list to be created from the entries in sheet2. Can anyone tell me how this can be done?


Posted by Colo on December 06, 2001 1:51 AM

Hi Donald.
:Can anyone tell me how this can be done?
If VBA("worksheet change event ") is used, it can be done.
Only you have to do is input the data in A1.

The cell for the input of Sheet1 is assumed to be A1.
Data is accumulated in A row of Sheet2.
It is assumed that the A1 cell of Sheet2 is a title.

To use this code, right click on your "sheet1" tab and select "View Code",
Paste the following code into there.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$A$1" Then Exit Sub
Sheets(2).Range("A65536").End(xlUp).Offset(1).Value = Target.Value
End Sub


Posted by Donald on December 06, 2001 3:04 PM

Thanks Colo
it worked!!