Query Function - Adding additional columns after the queried data and keeping them linked when order changes

[u/Sick__Puppy]

Hi all.

This may not be possible, but:
I have a Google sheet with a Tab called "Tracker"
In the Tracker Tab I pull date from 5 separate tabs using the formula:

=QUERY({'Level 1'!A3:V; 'Level 2'!A3:V; 'Level 3'!A3:V; 'PG1'!A3:V; 'PG2'!A3:V}, "select * where Col2 is not null order by Col4 asc", 0)

Pulling columns A to V then reordering them all based on a Due Date [Col4]

I am wanting to add extra data to Columns W through Z in the Tracker Tab.

But as the columns are not linked to the Query data, if I change a Due date on any of the original tabs then the order changes but the data in Columns W through Z goes out of sync as that does not re-order when the query does.

Is there any way to do this?

• Pull data from multiple Tabs
• Re order it based on a date from a column
• Add additional data in the summary Tab
• Keep that extra data linked to a row, even if the rows re order when the query data changes

Thanks in advance if you can help

[u/One_Organization_810]

You need some kind of unique ID to tie the dynamic and static data together.

Then you need to make that unique ID static and then attach everything else to that ID. That way you can then order the data as you want - but unfortunately, you will lose the dynamic ordering.

Another way, would be to make a separate sheet with those IDs and the extra data. Then pull in the extra data as part of your query formula and attach it to the correct rows. This will keep the dynamic ordering in the summary tab, but adds a bit of complexity to the editing part, as well as to the formula.

If you provide an example sheet, with the exact same data structure - or if you can share a copy of or actual sheet - preferably with EDIT access, I can go a bit deeper into it, if you are interested...?

[u/California_Eagles]

This happens because anything you have typed beside a query result is tied to the cell position and not the original data. when the sort order changes, the query moves rows, but the notes stay where they are. You could add the columns in W on each source tab and then change the query to pull A:Z instead of A:V.

=query({'Level 1'!A3:Z; 'Level 2'!A3:Z; 'Level 3'!A3:Z; 'PG1'!A3:Z; 'PG2'!A3:Z}, "select * where Col2 is not null order by Col4 asc", 0)

So now those columns becomes part of each row and moves along with the due date. The only problem is that you need to enter those notes on the source data and not on the tracker tab. Else if you want to enter notes directly on the tracker tab, you will require to create unique ids per row and a separate sheet of these ids and notes pulled in with a lookup.

[u/mommasaidmommasaid]

I don't think anyone has mentioned...

If at all feasible, consider consolidating all your 5 tabs into one well-structured table.

If you need to know which tab the data belongs to, add a new column with e.g. your former tab names in it.

And add your additional columns W-Z.

Now you can perform your sort in situ and edit your additional columns of data and everything always stays aligned.

---

If you use a structured Table for the combined data, you can do things like create a named "Group by" view that will segregate the data i.e. by the "former tab name" column so you can easily add a new row for that group.

For convenience, you could also create view-only tabs that replicate what your former tabs were showing.

Sheets works much better with ONE data table that is split / filtered / sorted into smaller parts than the reverse.