5

Is there an array function to copy an array? I use the workaround =TRANSPOSE(TRANSPOSE(A1:A30)) currently.

An example of when this is useful: you have a worksheet of raw data, and another worksheet with formulas applied to those values.

Doing it this way means that if a row is inserted in the first spreadsheet, it will be inserted in the second.

3

Short answer

Instead of =TRANSPOSE(TRANSPOSE(A1:A30)) consider to use ={A1:A30} as it's shorter.

Long answer

An alternative to ARRAYFORMULA is the use of embedded arrays also named

  • inline arrays
  • literal arrays
  • array parameters
  • "your own arrays"

Example of embedded array of constants

={"A","B";1,2}

Literal Array

={A1:B2;D1:E2;G1:H2}

Example of embedded array of non-adjacent ranges.

Embedded array

It's worth to say that for spreadsheets with regional settings that use:

  • . as the decimal separator should use , as the column delimiter
  • , as the decimal separator should use \ as the column delimiter

In both cases use ; as the row delimiter.

Reference

Using arrays in Google Sheets - Docs editors Help

| improve this answer | |
4

An ARRAYFORMULA would cover that. You can reference a range of columns like so:

=ARRAYFORMULA(Sheet1!A:H)

Complete columns are taken in to account, thus if something gets inserted on sheet 1, it will be included in sheet 2.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.