Google Spreadsheet, copy column in sheet to column in another sheet

Question

I'm trying to figure out how to have the entire contents of a column in one sheet show in another sheet.

For example I have column D in sheet2 that I want to show in column A of sheet1. Something like:

=ARRAYFORMULA(Sheet2!D)

But that gives me a syntax error.

score 87 · Accepted Answer · answered May 31 '13 at 00:14

87

Use:

=ArrayFormula('Sheet2'!D:D)

answered May 31 '13 at 00:14

AdamL

23,691
6
68
59

46

you can also use ={Sheet2!D:D} – jvdh Oct 04 '15 at 11:39
How can we select columns which are not in sequence? For example, how can I copy the columns A,C,E ? – user408108 Dec 01 '21 at 09:52

score 13 · Answer 2 · answered Aug 15 '16 at 09:43

If you want to share columns across separate files, you can use:

=importrange("17s-yYzX8GYvyA2PorY2PLaZ5OpFi14wU0ugAyTIrL24","Sheet1!G:J")

where the first string is the key of the sheet file that you want to import from. You can copy that directly from the URL e.g. https://docs.google.com/spreadsheets/d/17s-yYzX8GYvyA2PorY2PLaZ5OpFi14wU0ugAyTIrL24/edit#gid=0. The second string is the name of the sheet in the source file and in the example G:J means import columns G through J.

score 7 · Answer 3 · edited Jun 12 '19 at 13:11

7

={'Sheet2'!X:X}

Needs single-quotes around the sheet name.

edited Jun 12 '19 at 13:11

ross

2,684
2
13
22

answered Jun 12 '19 at 12:47

Roger Brownlie

71
1
1

1

`={(Sheet1!X:X)}` will work in this case too, but the quotes make it more clear – riddle_me_this Dec 09 '19 at 16:56

score 5 · Answer 4 · answered Feb 12 '19 at 19:51

5

More info

Both two formulas display the column like a Row, it "Transpose" the group of cells.

- ArrayFormula('Sheet'!X:X)
- Importrange("sheetkey","nameSheet!x:x")

But this beatifull formula copy the column without changing the direction.

- {Sheet2!X:X}

answered Feb 12 '19 at 19:51

Manuel Maestri

51
1
2

Brilliant, most solutions are way too complicated. This worked like a charm. – Benyam Jun 09 '21 at 10:52

Google Spreadsheet, copy column in sheet to column in another sheet

4 Answers4