CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
rvlookup
rvlookup

Reputation: 13

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

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?

Upvotes: 1

Views: 406

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 4

Related Questions