Excel: Dependant Data Validation Listing values from table with criteria without Macro

I would like to create a data validation field that draws values from a table, but only those values where a corresponding value matches a cell reference: the Month

Ideally this should be done dynamically, rather than having to create multiple named ranges.

I have included an image highlighting the target values I want to pull for January.

Edit: I am exploring using the FILTER formula, but no luck so far.

Edit 2: I got FILTER to work to provide the set of values I'm looking for, but it doesn't seem to want to work as data validation.

=FILTER(tblDate[Date],(tblDate[Month]=E2),"")

Solution 1:

As has been commented, the Data Validation list will not accept FILTER, but it will accept other formulae.

In the example, you can set the List validation as: =OFFSET(B2,MATCH(F2,A2:A18,0)-1,0,COUNTIF(A2:A18,F2)) keying off cell F2 for the value of the month.

NB: This works if the months are ordered (as in the given data). Also note that the List formula doesn't seem to like the Table[] syntax, so you have to put the ranges in as R1C1 format.

Alternatively you can use FILTER() for non-ordered data but put the results in a hidden column (Column H in the example below) on the spreadsheet. It is not as neat, but is more flexible, and allows the Table[] syntax.

eg

H2 = FILTER(Table1[Date],Table1[Month]=F2)

List Range

=$H$2#

(The # uses the result of the FILTER array function from cell $H$2: Hat-tip to @Ike)