How to keep cells "paired" with query results

Question

I am using an importrange query from other sheet to filter data: https://docs.google.com/spreadsheets/d/1FbeoKhiJ3jqYXqfIgAK_tHXPZ_Z1Xrm33ulu9gomXuE/edit?usp=sharing

The "colors" sheet has data populated from query ("items" sheet) and users are allowed to write comments on each row.

Problem: if row order is changed on "items" sheet, the "colors" sheet row order will be lost and any previous comment will not match the correct row.

How can I prevent this? Is there a way to keep paired query row results and comments? Maybe using timestamp as a reference?

At the moment sheet "colors" contains a correct pairing, once a row is inserted between "items", the pairing is lost

Answer 1

Suggested setup

Use two sheets, Import and Entry

1. Import sheet

   • Use to import the data to your spreadsheet,
     • Use IMPORTRANGE on this sheet

2. Entry sheet

   • Use to capture, insert comments

     • Add a column for key/id. Add the key/id manually, by writing one by one, using paste as values, a macro/script, add-on or an app that uses the Google Sheets API.

       • Related threads:
         • Copying data to *and from* another sheet based on the month of a date
         • Sync multiple cells so that if any one of them is changed, the others change as well
         • Sync two cells value

     • Use a lookup formula to get the required values from the import sheet by using the key/id column for the lookup, you could use VLOOKUP, INDEX/MATCH among other methods.

       • Related threads:
         • Align the rows of one sheet with the rows of an imported sheet

     • Add the columns that will hold the comments

"FAQ"

• What will happen if a row is deleted from the source sheet (the one that was imported)?
  • The cells on the Entry Sheet that use lookup will show #N/A but the key/id and the columns that hold the comments will remain in place.