0

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?

The_Train
  • 319
  • 3
  • 11
  • I will remember that one day @MikeSteelson - I have updated the original post with a link to a test variant of the spreadsheet. Editing rights are given so feel free to do as you wish with it – The_Train May 15 '22 at 10:19
  • Are you talking about empty rows there? If it is this then just let me know and I will reduce it down! The spreadsheet is data heavy to be fair and I am already wondering if Google Sheets will cope with it but I'm happy to persist with it as Google Sheets seems to offer more - basic stuff like being able to type to search in a dropdown which Excel doesn't offer unless you're a VBA expert. – The_Train May 15 '22 at 21:01
  • I will do that @MikeSteelson - I might not get to it for a couple of days but will most definitely feed back the outcome and accept as the answer if it does the trick! – The_Train May 16 '22 at 21:15
  • Disregard previous comment - decided to test it now and it does exactly what I require, thanks @MikeSteelson Appreciate you may not have the time, but if you do, would you be kind enough to explain the process here to assist with my learning and understanding of this? Prefer this approach than simply taking what I need without ever understanding what is happening – The_Train May 16 '22 at 21:37
  • Thanks for the further information @MikeSteelson - I wasn't far off in my working out of how that query works! If I wanted to adapt the formula to include an "All" option which would in essence remove the query and just show all items, would this also be possible? (apologies if I should start this as a new question) – The_Train May 23 '22 at 19:48
  • Thanks @MikeSteelson, really appreciate your efforts on that. Everything is working as I desire now – The_Train May 24 '22 at 20:38
  • Sorry to bother you again @MikeSteelson but I have encountered a slight issue which I am not sure if is formula related or layout related. Basically in the number column, most of the data is purely numerical but some starts with a letter and then has numbers behind it! Where there is a letter at the start, the record is appearing in the filter query but the number cell is empty - just wondering if you have any immediate suggestions to solve this please? – The_Train May 28 '22 at 13:00
  • Hi @MikeSteelson - I thought I would update further as I am now considering an addition to my sheet which would enable to filter by specific dates as well as by all of a year and all of a month I have updated the original post with an edit to detail this further..... – The_Train May 30 '22 at 20:43
  • Sorry but I can't work with your spreadsheet. Can you remove all rows after 1000 please? – Mike Steelson May 31 '22 at 02:45
  • After a long search, it seems that it is not possible to simply mix all the alternatives you need into one criterion. Perhaps you need to enter the start and end of the period in two cells. – Mike Steelson Jun 01 '22 at 01:57
  • Now you mention it @MikeSteelson, a start/end date would offer a bit more flexibility. I've used https://docs.google.com/spreadsheets/d/1wTWuvFwMTqJ-sjIZbXWpGOS1WKwpODj2R8KAzqlqkuw/edit?usp=sharing to adapt and have got a filter working for the date range (S1:W1 on 2022 Stats is where the filter choices are) I've also added 2 other dropdowns on the 2022 Stats sheet - would it be possible to filter by either date range, V1 dropdown, W1 dropdown in any combo so filter by date range & dropdowns or filter by just 1 or 2 of them? – The_Train Jun 01 '22 at 21:33

1 Answers1

1

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)
Mike Steelson
  • 14,650
  • 2
  • 5
  • 20