Search code examples
google-sheets-formula

Multiple filter, query and importrange Google Sheets


I have a Google form with multiple sections that return responses to a Google sheet. Based on the sections, the data is returned to varying columns in the response sheet. I have a summary sheet that pulls select column data and orders it for ease of use. The problem is, my current formula imports from the response sheet in the order the filter(query(importrange formula is written, thereby grouping each form section response by date, then proceeding to import the next form section data. Is there a way to make it query the data and return results by all the section dates?

EDIT: The colored cells are representative of the sections in the Google form. Instead of the date ranges restarting, I would like all data to be sorted by date.

I have tried to nest INDEX, FILTER and other methods to no avail. Here is a sample form sheet and the summarized sheet.


Solution

  • You may try this in Cell_B7 of Summarized Sheet:

    =let(Σ,importrange("1mlhbWb3kh12zbV6u3cKxzvIAQDHd8hYeDZOdPWrxJUQ","A2:S"),
         Λ,vstack(choosecols(Σ,1,13,14,10,12),choosecols(Σ,1,7,8,3,6),choosecols(Σ,1,18,19,2,17)),
         sort(filter(Λ,index(Λ,,2)=B2),1,1))
    
    • Change the final 1 to 0 if you want the dates to sort by descending