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
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?