Formula for copying a column of cells into another sheet

[kitchen]

Hi there,

I'm very new to Excel, and I'm having trouble figuring a few things out. Hopefully this will be very easy for you guys!

In Sheet 1, I have a column of cells that I would like to also appear in Sheet 2. If I add a new row to the column in Sheet 1, I would also like it to be updated automatically in Sheet 2. Currently, I can get it to show the contents of individual cells from Sheet 1 in Sheet 2 by using this formula in the formula bar for each cell in Sheet 2:

=Sheet1!A3 (or whichever cell it is)

That's fine, but I'd like to just have a formula that will reproduce the entire column (ie. without a fixed range, as new rows are going to be added to the column).

If anyone could point me in the right direction, I'd be very very grateful. Thanks.

 

[Purple Youko]

2 possible ways

1) repeat teh formula that you have in all the relevent cells in sheet 2
2) put this function in your sheet1 code window

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim colnum As Integer
  Dim rownum As Integer
 
  colnum = Target.Column
  rownum = Target.Row
  
  Sheets("sheet2").Cells(rownum, colnum).Value = Cells(rownum, colnum).Value
End Sub