Answer the question
In order to leave comments, you need to log in
Formula for the entire column for an updated table in one cell - is it possible?
Good afternoon.
There is a table where the survey results collected through a Google form regularly arrive.
The data from this table is used further in other tables, along the chain.
Submitters can change the submitted data any number of times by simply submitting a new form. Identification occurs by some feature in one of the form fields or a combination of features. (let it be a name, for example)
The tasks are as follows:
1. Mark all obsolete lines (for example, with the character "1" in a new column), if there are several answers from the same user. (implementation example on the screenshot)
Or in the opposite way - mark as the most recent version. (I have not yet figured out how, but it would be interesting to know)
2. Do it automatically so that you do not have to manually stretch the formulas after loading new data from the form.
It is impossible to simply stretch the formula on the sheet with the results in advance, because the data from the google form overwrites the contents of the entire row each time. It is possible, in theory, to copy the data to an intermediate sheet via importrange() and stretch the formulas in neighboring columns there beforehand, but the link to the current sheet is already written in many places and I don’t really want to change it live. And besides, maybe there is simply a more compact solution?
It seems that I previously saw a solution based on ArrayFormula () written in the top cell of the column once.
3. If item 2 is real, make a similar single formula based on concatenate () for a new column from the values of the existing ones (to get Masha10, Pasha 24, etc.)
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question