Google-sheets – Importing answers from a Google Form into another Google Sheet, then adding a column for notes

google sheetsgoogle-sheets-query

I have been using a Google Spreadsheet with a master list created from a Google Form. I created a new tab to rearrange the order of the entries to the newest ones on top.

However after those cells have been copied over and new entries are created from the form, if I put notes next to those cells, the cells (that were copied over) move down with each new entry though my notes column stays. It doesn't move down as well with the row I put it on. How do I fix this?

I haven't been able to find any examples. The imported data is the only thing moving down with new entries, while my working cells next to them don't move. Please advise.

Here is the formula I was using:

=query(Master!A2:I, "select * order by A desc", 1)

Best Answer

Notes are bound to the address of the cell and not to the content of the cell.

A note is metadata to the cell. So, I am afraid you cannot just fix it.

When a new response arrives, a new line is not added. It is just new content added after the latest content, leaving the exising content untouched.

When you execute the query you "rearrange" the position of the content of the cells. Not the cells. The same happens when executing any formula for that matter.

A comparison

When you think of notes, think of formatting.
Formatting is bound to the cell. Not the content. That is why, when for example cell B3 is filled with green, text bolded and assigned a date format, it will still retain these characteristics no matter the content.

The "exception"

Not really one. Just the confirmation to the rule. Think of the following 2 scenarios:

Select a cell (single click). cmd+C or Ctrl+C to copy. Next, cmd+V or Ctrl+V to paste. Both formatting and content get transferred.
Select the content of a cell (double click). cmd+C or Ctrl+C to copy. Next, cmd+V or Ctrl+V to paste. Just the content tranferred, free from formatting.

Notes behave the same way. When you for example, place a note in cell B2 it stays in B2 while data is added, altered or shifted around in the sheet through formulas. If you want to move it, you have to manually Copy+Paste it to different cell.

Related Solutions

Google-sheets – Automatically adjust corresponding cells when using “=query”

The values added to the right of QUERY() result aren't "tied" to the source range.

To "tie" them, add these values to the source sheet, as was suggested in a comment posted by @pnuts.

Google-sheets – Google Form keeps changing Spreadsheet cell entry

The problem was due to Google Forms adding a new row every time.

If you are having the same problem here's what you need to do to fix it:

Where you previously had your formula change it to:

=arrayformula('YOUR SHEET NAME'!A2:A)

Change the sheet name, and enter the cell you want to target, as-well as the column.

Make sure you delete all other formulas in that column.

This will automatically post the new data from the cell into the one below it. Just make sure you have no other formulas in the column. Also make sure the cell you target is the one where the form data is stored.