How to get the source of an ARRAY formula combined with QUERY in Google Sheets

Question

I have a Spreadsheet where each tab is a month (January, February, ..., December) and they have exactly the same structure.

In order to combine all the information in a single sheet I used an array:

={January!A5:Q;February!A5:Q;March!A5:Q;April!A5:Q;May!A5:Q;June!A5:Q;July!A5:Q;August!A5:Q;September!A5:Q;October!A5:Q;November!A5:Q;December!A5:Q}

But I wanted to filter the entries of each month based on some conditions, so I used the QUERY function:

=QUERY({January!A5:Q;February!A5:Q;March!A5:Q;April!A5:Q;May!A5:Q;June!A5:Q;July!A5:Q;August!A5:Q;September!A5:Q;October!A5:Q;November!A5:Q;December!A5:Q},"select * where Col3 = 'X'",0)

I get the desired result. The extra plus that I want to achieve is to identify the month at each line belongs, in other words, the sheet were the data is pulled. And I want to see that without adding columns or extra fields on the source sheets.

How do I do that?

@player0 Here it is: imgur.com/VflSrRM. I want to, somehow, automatically get the month for each filtered line. — rvlookup
– rvlookup, Commented Mar 26, 2020 at 18:01

player0 · Accepted Answer · 2020-03-25 11:38:53Z

4

try:

=ARRAYFORMULA(QUERY({
 January!A5:Q,   January!Z5:Z&"January"; 
 February!A5:Q,  February!Z5:Z&"February"; 
 March!A5:Q,     March!Z5:Z&"March";     
 April!A5:Q,     April!Z5:Z&"April"; 
 May!A5:Q,       May!Z5:Z&"May";
 June!A5:Q,      June!Z5:Z&"June"; 
 July!A5:Q,      July!Z5:Z&"July"; 
 August!A5:Q,    August!Z5:Z&"August";  
 September!A5:Q, September!Z5:Z&"September";
 October!A5:Q,   October!Z5:Z&"October";
 November!A5:Q,  November!Z5:Z&"November";
 December!A5:Q,  December!Z5:Z&"December"}
 "where Col3 = 'X'", 0))

where column Z is an empty column

answered Mar 25, 2020 at 11:38

player0

131k14 gold badges91 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to get the source of an ARRAY formula combined with QUERY in Google Sheets

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related