Data Validation to only include certain date dependant rows from a dataset

[OobuJoobu]

Hi,

using my example screenshot, I need to create Data Validation to show a list only of those items in column P where the date in column Q is today or in the future. So in this example I would only want to see the column P items numbered 5,7 and 8 as they're future dates (based on today's date as I type of 12th September)

I would need this to be dynamic, so that when we hit 21st September, the item numbered 5 would no longer appear in the dropdown, and also dynamic so that when new items are added to this list that happen to have a future date, they will be picked up in the data validation dropdown.

I'm not even sure if this is possible, any advice please? If it's not possible as regular Data Validation then I will instead write some VBA that will do this on a the press of a button by the users, but would prefer to avoid that if possible.

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this would be any use.
Make that table of data into a formal Excel table. Look on the Insert ribbon tab - Table

Use a formula like I have in S4 and then Data Validation as shown in cell U4

OobuJoobu.xlsm
	P	Q	R	S	T	U
1
2
3	Place	Date		DV List		Choose Place
4	London 01	01 April 2023		Glasgow 01
5	leeds 01	23 May 2023		London 03
6	Norwich 01	10 August 2023		Norwich 02
7	London 02	01 September 2023
8	Glasgow 01	20 September 2023
9	Leeds 02	02 September 2023
10	London 03	01 October 2023
11	Norwich 02	01 November 2023
12
13
Sheet1

Cell Formulas
Range	Formula
S4:S6	S4	=FILTER(Table1[Place],Table1[Date]>=TODAY(),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
U4	List	=S$4#

Here is what you see in the U4 DV drop-down.

 

[OobuJoobu]

Welcome to the MrExcel board!

See if this would be any use.
Make that table of data into a formal Excel table. Look on the Insert ribbon tab - Table

Use a formula like I have in S4 and then Data Validation as shown in cell U4

OobuJoobu.xlsm
	P	Q	R	S	T	U
1
2
3	Place	Date		DV List		Choose Place
4	London 01	01 April 2023		Glasgow 01
5	leeds 01	23 May 2023		London 03
6	Norwich 01	10 August 2023		Norwich 02
7	London 02	01 September 2023
8	Glasgow 01	20 September 2023
9	Leeds 02	02 September 2023
10	London 03	01 October 2023
11	Norwich 02	01 November 2023
12
13
Sheet1

Cell Formulas
Range	Formula
S4:S6	S4	=FILTER(Table1[Place],Table1[Date]>=TODAY(),"")
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
U4	List	=S$4#

Here is what you see in the U4 DV drop-down.

View attachment 98596

Genius, works perfectly, thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.