How can I import a column from one sheet into another sheet of the same Google Sheets spreadsheet?

Question

I don't look for ImportRange because I want to stay within the same Google Sheets spreadsheet. Instead, I want to import a column from one sheet into a column of another sheet within the same spreadsheet.

Answer 1

One way to do this is with ArrayFormula. If the sheet you want to import from is named Sheet1 and you want to select the entirety of column A, you can do the following:

=ArrayFormula('Sheet1'!A:A)

You can see an example of this in action here: https://docs.google.com/spreadsheets/d/1kJraNDzrLF8-W_BxbL8pU6e_QuoMqFcl_sgyzT14L2Y/edit?usp=sharing

In general, you can use the form [SheetName]![Range] to pull in data from another sheet from within the same spreadsheet. In this case, ArrayFormula is needed to expand the entire range instead of just importing the first cell in the range.

Answer 2

Also possible:

=QUERY(Sheet1!A:A)

Answer 3

I don't look for =ImportRange because I want to stay within the same Google Sheets spreadsheet.

seems to me to be repetition of a misconception I think I have seen elsewhere. IMPORTRANGE works to fetch data from the same document or even the same sheet.

It may not be the best solution is all cases, but it is functional. For example:

=importrange(" k e y ","Sheet1!A:A")

will copy ColumnA to any other chosen (available) column even when Sheet1 is the only sheet in the document. However authorisation is still required.

A possible use case is where reference to the column on the extreme left is to be maintained even after insertion of a new ColumnA. (In the formula Sheet1!A:A does not auto adjust when a column is inserted on the extreme left, it remains Sheet1!A:A.)

Answer 4

Suboptimal, but also possible:

=TRANSPOSE(TRANSPOSE(Sheet1!A:A))