CODEWITHSUNDEEP
google-sheets-apiarray-formulas
Keith D
Keith D

Reputation: 413

fix for moving rows in google-spredsheet breaks array formula

I have an array formula in google sheets for an entire column, e.g. the following formula in C1

ArrayFormula(A1:A+B1:B)

And there is data in columns A and B.

If I were to grab a row and move it to another location. As soon as I move it the respective value in column C of that row is pasted as hard value and breaks the entire array formula.

Is there a way around this?

Upvotes: 4

Views: 2368

Answers (2)

Maxime Pacary
Maxime Pacary

Reputation: 23041

Workaround found.

I was doing a manual sort which changed the row of my ARRAYFORMULA(), but has the same consequence as drag-n-dropping that "special row".

You will have to work with two sheets however.

  • Suppose that in your original data sheet (sheet 1), your have data on two columns (A and B), and you want to use ARRAYFORMULA() on column C, like in your example.

  • Leave sheet 1 "as is", create another sheet (sheet 2) and in top left cell type this:

    ={A1:B}

  • In sheet 2's column C, one cell below top (to leave room for header), enter:

    =ARRAYFORMULA(A2:A+B2:B)

Then you can sort data as you wish in sheet 1, and ARRAYFORMULA() will always work in sheet 2 👍

Upvotes: 0

pointNclick
pointNclick

Reputation: 1632

Unfortunately, there is no simple way around it. With arrays, the formula is usually tied with the positioning of the values as results vary according to the position of each value in an array. Hence, moving anything will result in the distortion of the formula.

The only (simple) way around it is to move your values by the cut-copy-paste method instead of dragging the whole row around. OR (For a more robust but complex implementation) write a script for Custom Functions in your sheet which will perform the necessary calculations and will not be affected when you move the values as it takes inputs from cell positions that have been pre-defined in the script.

Upvotes: 2

Related Questions