2

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?

  • @marikamitsos Tags should not be used on titles Ref. webapps.stackexchange.com/help/tagging (instead of fixing "Google sheets:" it should be removed) – Rubén Sep 18 at 15:21
  • @Rubén Missed that bit. Thank you. – marikamitsos Sep 18 at 15:24
  • @Rubén Should this title -or similar ones- be edited as well? – marikamitsos Sep 18 at 15:39
  • @marikamitsos We should avoid to do minimal changes that doesn't represent a relevant improvement . If you will edit that question to make it clearer, edit the tags to make if easy to be found for experts, then remove the tag from the title too. – Rubén Sep 18 at 15:55
  • Have you tried the solution offered? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Sep 19 at 23:41
2

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

  • 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 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 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? – marikamitsos Sep 20 at 10:39
  • The numbers please – sam Sep 20 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 at 11:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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