Dynamically reference data from multiple tabs and sort it

Question

I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity.

I've created a copy of the spreadsheet HERE (please use file > copy) if you would like to have a copy of the file to edit.

I want to reference all the data into a "master sheet" and then sort it. But I want the data to be referenced eg. if it changes in the original tab, the "master sheet" should update also.

Currently I've tried doing this 3 ways :

Option 1.
Reference all the data into the master tab using =Sheetname!B2 and repeat for each sheet. This works to view all the data on one page, but once the data is in it does not allow me to sort it.

Option 2.
Reference all the data into the master tab using =Sheetname!B2 and repeat for each sheet. Then create a pivot table from the master tab, this works, and allows me to sort, but only sorts the quantity within each category. Not as a full list.

Option 3.
The only way I've got this to work so far, is to do option 1, but then copy the data and paste as values. This works, but is no longer dynamically referenced.

Is there another way I could dynamically reference all the data and then sort it?

Answer 1

Use curly brackets within this very simple query:

=QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col2")

The trick here is the curly brackets {....} we use to create and combine the arrays from the different tabs.

Extra tip:
{Alpha!A2:B;Beta!A2:B;Charlie!A2:B}
A semicolon ; combines vertically (in columns, one under the other).

{Alpha!A2:B;Beta!A2:B;Charlie!A2:B}</code></pre></b>
A comma , combines horizontally (in rows, one next to the other).

The use of the semicolon and comma interchange places depending on your locale.

---

EDIT (following your comment)

One can use an additional parameter for order by: order by Col1, Col2
Please note that order by defaults to ascending asc. That means that it can be omitted (as we did here).
On the other hand, descending desc must be included (order by Col1, Col2 desc)

=QUERY({Alpha!A2:B;Beta!A2:B;Charlie!A2:B},"select * where Col1 <>'' order by Col1, Col2")