There are two spreadsheets that are used by two teams, Team A and Team B. Team A provides directions and posts work orders to Team B. Team B in turn enters comments on the actions completed. Each team only has access to their respective spreadsheet.

The data is copied between the two spreadsheets using query() and importrange(). This is the formula I use to share data with Team B (Group Workers)

=QUERY(IMPORTRANGE("Sheet-ID","Name_of_sheet!A1:U1000"),"Select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col18,Col19,Col20 WHERE Col19= 'Worker 1' OR Col19= 'Worker 2' OR Col19= 'Worker 3'")

Example Table Sheet B

Coming From Sheet A-Data1 From Sheet A - What to Do From Sheet A- Worker Name On Sheet B- Worker's Comment
Unique One Some work 1 Worker 1 Some work 1 was done
Unique Two Some work 2 Worker 2 Blank Cell
Unique Three Some work 3 Worker 3 Working on Some work 3

The problem is that we need to preserve Team B's comments, or somehow link them to the work orders coming from Team A, so that even if a row in Team A's spreadsheet is inserted or removed, Team B's comments must remain associated with the original task.

But the formula changes its results dynamically, and data in Team A's spreadsheet keeps updating, with the outcome that comments no longer display in the same row as the work order they are supposed to be associated with. How can I keep comments in the same row as the work order they relate to?


Your current setup exposes you to a definite risk of rows getting misaligned.

It would be easiest to share just one spreadsheet between the two teams, since that would let you avoid the issue altogether.

One easy solution would be to store all the data in the same tab and use filter views to decide which rows to show at a time. This feature lets multiple simultaneous users sort, filter and edit the sheet without disturbing each other.

For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

If you do not want to use one shared spreadsheet, even with filter views, see Lance's treatment of the row misalignment issue and how it can be dealt with in some cases.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.