Filtered Drop Down List from named ranged

[Kramxel]

Hi All,

I have attached an example for you:

I have two columns of days of week in one and dates in the the second. What I am looking for is to be able to create a data validation drop down list from these columns that specifically contains each "Monday" within the range. In essence creating drop down list of the dates (column b) which fall on a Monday.

[NeedForExcel]

Here!

Put this in D1 and drag down as required.

=IFERROR(INDEX($B$1:$B$60,SMALL(IF($A$1:$A$69="Mon",ROW($A$1:$A$69)-ROW($A$1)+1,""),ROWS($D$1:D1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

On F1 Create a Drop Down Using Data Validation, and for the source use this formula

=OFFSET($D$1,0,0,COUNTA($D$1:$D$69)-COUNTBLANK($D$1:$D$69))

See the file attached..

[Kramxel]

Hi NeedForExcel,

Thank you for the formulas - quite literally legendary

[sktneer]

I approached it like this...