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

1
votes

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?

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
share a copy of your sheet – player0
@player0 Here it is: imgur.com/VflSrRM. I want to, somehow, automatically get the month for each filtered line. – rvlookup
did you try the answer? – player0
webapps.stackexchange.com/help/someone-answers – player0

1 Answers

3
votes

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