google sheets - formula to create an "instance" of a column? similar to SORT but without s-CodePudding

I have a simple column with a list of values, that can be of variable length. I want to have a "live" instance of that column in another sheet.

I have succeeded with using the obvious formula like this on the target sheet:

=SOURCE_SHEET_WITH_DATA!:A1
=SOURCE_SHEET_WITH_DATA!:A2
=SOURCE_SHEET_WITH_DATA!:A3 etc..

This works fine, but unfortunately I need to periodically delete and insert rows on my target sheet, and this seems to completely break the indexed references to the rows, so I have to "fix" the connections again afterwards each time and there is much room for human error.

The SORT function does almost exactly what I want, whereby only the 1st row of the target sheet needs to hold the sort formula, so removing or appending rows underneath it isn't an issue, but then of course it sorts the data which is undesired.

Does anyone have any ideas of a better way to do this?

CodePudding user response：

delete all formulae in your column and use:

={SOURCE_SHEET_WITH_DATA!A1:A10}

or for open range:

={SOURCE_SHEET_WITH_DATA!A1:A}