4

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?

0

1 Answer 1

4

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")

10
  • Thanks @marikamitsos that does exactly what i was looking for. Is there also a parameter that will allow me to change the to sort, by ascending or descending ?
    – sam
    Sep 20, 2019 at 10:22
  • Ive also updated the google sheet shared in my question to add in your answer in tab "marikamitsos answer"
    – sam
    Sep 20, 2019 at 10:23
  • "...a parameter that will allow me to change the to sort, by ascending or descending". Ascending which column? The name or the values/numbers? Sep 20, 2019 at 10:39
  • The numbers please
    – sam
    Sep 20, 2019 at 10:53
  • Just found the answer, we can DESC to the end of the query. eg. select * where Col1 <>'' order by Col2 desc
    – sam
    Sep 20, 2019 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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