I would like to auto-populate a column with an identical column in another spreadsheet (in the same file). I need this to update as the other sheet is being worked on.

So far I have tried copying and pasting the content across as a link - this works for one cell being copied to another but the auto-update fails if I copy the whole column. The differences in formula are:

For one cell copied only:

='sheet1!$C$2

For multiple cells copied:

=''sheet1'!C2

So I get that the $ needs to be in there - but how do I do this for the whole column?