O
O
Oleg Surovyagin2018-07-24 02:39:32
Google Sheets
Oleg Surovyagin, 2018-07-24 02:39:32

The form inserts a new line, how to stretch the formula into it automatically?

Friends, I do accounting on Google form (I understand how wild it is, alas).
The form, when filled out, creates a new line in the attached google shield.
It is necessary that in the columns to the right of the data array formulas are not deleted from the form.
That is, the form enters column X, and in column Z I have a formula that needs to process the values ​​entered from the form. And the formula is not stored there, as a new line is added. The formulas below remain.
PS I tried to solve it through a pivot table with a calculated field, without success

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
MrKot2007, 2018-07-24
@MrKot2007

I searched for a solution for a long time, but I did not find anything worthwhile.
I managed to solve it like this:
On the sheet with the data from the form, create a named range, for example "Data", which includes all columns and rows with a margin (for data that will come in the future)
On another sheet, where we process the data, write the formula =Index( Data) and our data from the form are translated into a certain part of the second sheet, BUT now they are updated when new data appears and the formulas on the second sheet do not shift.
The solution is not the most elegant, but it works. At least my problem was solved.

A
Alexey Nikolaev, 2018-07-28
@AlNy

Have you tried using ARRAYFORMULA? I used in similar tasks. For example, you can enter a formula like =ARRAYFORMULA(A1:A+B1:B)
once in one (!) cell C1 , and A and B will be summed up in column C, regardless of how many records users enter, whether they insert in the middle of new lines, etc.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question