CODEWITHSUNDEEP
google-sheets-formula
The_Train
The_Train

Reputation: 341

Google Sheets Query using dependent dropdown

Initially I set up a query based on a search by month and year in a single dropdown using this formula

=QUERY(Haulage!A2:R, "SELECT * WHERE 1=1 "&IF(A2="All Months",""," AND LOWER(M) = LOWER('"&A2&"')"), 1)

This works great but I have now adapted my dropdowns to become a dependent dropdown with the first dropdown being by year which then filters the 2nd dropdown by that year. In the second dropdown I want to include an "All 2022" option which will then filter my query by everything with 2022 in the date.

I am struggling to adapt my formula to be able to make this work though so would appreciate any help

Edit: Link to a test variant of the spreadsheet - https://docs.google.com/spreadsheets/d/1AewSFMxQjnYD44rkPk6h7TGlio2bwm8hv_mAkGiaEwA/edit?usp=sharing

Note that the dependent dropdowns are the wrong way around at the moment - so the J1 dropdown triggers the A1 dropdown and then the selection in A1 transfers to the "Date Filter" sheet

Edit - 30/05/22:

Looking to build on and adapt the above to include a filter by specific date as well via a 3rd dependent dropdown.

It's in the initial stage in my head but my initial wondering is whether the filter can be applied using 3 different dropdowns. So I would have dropdown1 for YEAR, dropdown2 as MONTH & YEAR and then dropdown3 as DATE each dependent on the previous one.

Would it be possible to filter by year when dropdown1 is selected, then by month if a selection is made in dropdown2 and then by specific date if a selection is made in dropdown3 - ie I select 2022 in dropdown1 and all 2022 records are filtered, I then select May 2022 in dropdown2 and this changes the filter to records from May 2022 and then I select 2nd May 2022 from dropdown3 and that filters down to records from that date only?

Upvotes: 0

Views: 860

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15328

Try

=QUERY(Haulage!A2:R, "SELECT * WHERE " & if(A2="All","A is not null",if(left(A2,3)="All","(M) LIKE ('%"&SUBSTITUTE(A2,"All","")&"')","LOWER(M) = LOWER('"&A2&"') ")) , 1)

enter image description here

the sentence is if(A2="All","A is not null",if(left(A2,3)="All","(M) LIKE ('%"&SUBSTITUTE(A2,"All","")&"')","LOWER(M) = LOWER('"&A2&"') ")) it means that

  • if A2 is All, all rows will be selected when A is not null
  • if A2 starts with All, we will keep all rows containing A2 without All (like %yyyy),
  • and if A2 does not start with All, we will keep rows with an exact value than A2

edit

if you encounter mixed values ​​(numeric and string), apply to_text and change A to Col1, B to Col2 etc. as follows

=QUERY(arrayformula(to_text(Haulage!A2:R)), "SELECT * WHERE " & if(A2="All","Col1 is not null",if(left(A2,3)="All","Col13 LIKE ('%"&SUBSTITUTE(A2,"All","")&"')","LOWER(Col13) = LOWER('"&A2&"') ")) , 1)

Upvotes: 1

Related Questions