I am trying to create a combined shopping list by collating all the data from various tabs within the sheet. But not sure how to pull tab name and potentially INDIRECT data from QUERY.

enter image description here

These shopping list tabs will all be laid out the same. See my google sheet below
enter image description here

I have used the query function to pull this data, ‘combined list’. I have made an additional column to track progress of the order. I tested to see if this got messed up when I added new data into the other shopping list tabs but seems ok.

Ideally, I would like to be able to pull through the tab name and potentially the cost code so I know where that data line came from and the date it was added. This will help when I filter by supplier and by tab name.

On every shopping list tab, the tab name is written in the H1 cell. Maybe INDIRECT would work for this? But I am not sure if it would work with the QUERY function?

Happy for this to be a google script. As I recently did a TOC for another spreadsheet, and I can grasp what is written but just not sure how to start writing it for myself.

https://docs.google.com/spreadsheets/d/19KCA6uyFlF97kLjkljmEzUDh5lUoYhVg-FyFSJXOOrU/edit?usp=sharing

Thank you! 🙂