Dynamic Data Validation ignoring cells with #N/A

[aaa324]

Hi, I have a row with month names from Nov18 to Dec21 in the data validation. I want the data validation to ignore future months. I can manage to filter out future months with dates between November 2018 and end date =EOMONTH(TODAY(),0). But how can I get a pull-down list of those months at the same time? I prefer a non-VBA solution if possible. Thank you

DashboardData.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
1	All Months			Oct18	Nov18	Dec18	Jan19	Feb19	Mar19	Apr19	May19	Jun19	Jul19	Aug19	Sep19	Oct19	Nov19	Dec19	Jan20	Feb20	Mar20	Apr20	May20	Jun20	Jul20	Aug20	Sep20	Oct20	Nov20	Dec20	Jan21	Feb21	Mar21	Apr21	May21	Jun21	Jul21	Aug21	Sep21	Oct21	Nov21	Dec21
2
3	Select Month	Aug21		Oct18	Nov18	Dec18	Jan19	Feb19	Mar19	Apr19	May19	Jun19	Jul19	Aug19	Sep19	Oct19	Nov19	Dec19	Jan20	Feb20	Mar20	Apr20	May20	Jun20	Jul20	Aug20	Sep20	Oct20	Nov20	Dec20	Jan21	Feb21	Mar21	Apr21	May21	Jun21	Jul21	Aug21	#N/A	#N/A	#N/A	#N/A
DashboardData

Cell Formulas
Range	Formula
D3:AP3	D3	=IF(D$1>$B$3,#N/A,EOMONTH(D$1,0))

Named Ranges
Name	Refers To	Cells
Portfolio	=DashboardData!$D$1:$AP$1	D3

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=$D$3:$AP$3

 

[StephenCrump]

Welcome to the Forum!

Perhaps ...

	A	B	C	D	E	F	G	H	I	J	K
1	StartDate
2	15 Feb 2021		Feb 21	Mar 21	Apr 21	May 21	Jun 21	Jul 21	Aug 21	Sep 21
3
4
5
6
Sheet3

Cell Formulas
Range	Formula
C2:J2	C2	=EOMONTH(StartDate,SEQUENCE(,1+DATEDIF(EOMONTH(StartDate,0)+1,EOMONTH(TODAY(),0)+1,"m"),0))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
StartDate	=Sheet3!$A$2	C2

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=C2#

 

[aaa324]

A really good forum which I will use more for sure. Many thanks. That did it!

 

[StephenCrump]

Great. Thanks for posting back.