I have a Google Sheets document with my video game collection. Each console system has its own sheet, but I decided to consolidate all sheets into one new sheet as an overview using a query that looks something like this:
=query({NES!A2:C;SNES!A2:C},"Select Col1, Col2, Col3 where Col1 is not null order by Col2 desc", 1)
This works fine, and I’m satisfied with it. However, my collection is quite large and spans over 20 consoles, so sometimes I see a game and I forget which system I have it for. So I would like to add a 4th column with the name of the sheet that the row came from (since each sheet is named after a console).
For extra points, it would be awesome if every console had it’s own font/ or background-color, maybe the colors that Google Sheets assigns to the sheet range but it can also be manual, doesn’t really matter, just as long as they’re different colors.
Advertisement
Answer
the easiest you can do is to build VR table:
=ARRAYFORMULA(QUERY(
{{NES!A2:C, TRANSPOSE(SPLIT(REPT("NES"&"♦", ROWS(NES!A2:A)), "♦"))};
{SNES!A2:C, TRANSPOSE(SPLIT(REPT("SNES"&"♦", ROWS(SNES!A2:A)), "♦"))}},
"where Col1 is not null order by Col2 desc"))
