Autofill a column in one sheet with the values from a column in another

Question

I have two sheets and in Sheet 2 Column A I want to duplicate all of the values from Sheet 1 column A so that when I add a new value to Sheet1 Column A it displays in sheet 2. There are roughly 30000 rows so I don't want to have to drag the fill handle each time.

I thought I could do this by defining a name giving it a Range of Sheet1!A:A but I can't figure out how to get this to fill the entire column in Sheet2.

Hope this makes sense.

Answer 1

If you make Sheet1!A a table you can always simply create a pivot table for Sheet2 and you're done! The only caveat is that the data in Sheet2 will have to be in column B

Here's a quick snapshot of what I did to get this to work:

Sheet1

1. Column1(A) is the data
2. Column2(B) is a formula (=ROW([@Column1])-1); that keeps the current order in tact.

Sheet1

Sheet2

3. Insert pivot table and add table both columns from Table1 in Sheet1
4. Order the Row Labels like so: Column2, Column1 Row Labels
5. Change Field Settings in Row Labels > Column2 to 'Show item labels in tabular form' Field Settings
6. Hide column A in Sheet2

Lastly, refreshing the data. You can refresh the data by using the shortcut key Alt+F5 or you can set it to automatically refresh when opening the file: PivotTable Tools > Options > Options > Data > Refresh data when opening the file