CODEWITHSUNDEEP
google-sheets
KOVIKO
KOVIKO

Reputation: 1379

How do I reference one cell created in ARRAYFORMULA from another?

Let's say I'm using ARRAYFORMULA to generate values (whitespace is for readability):

=ARRAYFORMULA({
  Sheet1!A1:C,
  Sheet1!A1:A * Sheet1:B1:B,
  Sheet1!A1:A * Sheet1:B1:B + Sheet1!C1:C
})

Instead of needing to re-calculate the 4th cell in the 5th cell, is there a way to reference the result of the 4th cell in the 5th cell?

Upvotes: 0

Views: 424

Answers (1)

user6655984
user6655984

Reputation:

No, there is no way to reference a column of virtual array {} within the same array. Two workarounds are:

  • Create the column A*B+C using a second array formula, which references the output of the first

or,

  • Wrap the output of arrayformula in a query which can reference the columns as Col1, Col2, ...

Example:

=query(arrayformula({A1:C, A1:A*B1:B}), "select Col1, Col2, Col3, Col4, Col4+Col3 label Col4+Col3 ''", 0)

Upvotes: 2

Related Questions