• You are losing out on amazing benefits because you are not a member. Join for free. Register now.
  • Two-Day Giveaway - Win a Google Chromecast, Branded Gear, and a Mystery Gadget. Enter Here.
  • Giveaway - Win a DiskStation DS918+ and Active Backup Suite from Synology. Enter Here.

Excel: Data Validation

rh1

Well-Known Member
Joined
Aug 5, 2011
Messages
396
#1
Quick question to you excel fundis as Google let me down.

I have column of calculated dates which range from current to plus minus 5 years to the future. When using data validation all the dates come up. From which only 10 dates would be relevant at any given time. As time goes on the later dates would be relevant but scrolling down would be a pain in the ass.

Is there any way to limit the dates to later than a specific date and maybe the next10 max 20 entries after the date.

In other words a formula which look at a date (date x) on one sheet (Sheet a), then select from a specific column in Sheet b the 1st date after this date (date x) as well as the following 9-11 entries. These dates would then populated in column in sheet c.

I would then use data validation (list) to this column of dates.
 

KT-B

Honorary Master
Joined
Feb 3, 2014
Messages
10,849
#2
You can do this with VBA but not sure you can do it strictly with data validation. I am assuming you want the list to show date +10 consecutive dates? You could basically only need code to capture the selected/current date. Forumulas could be used to create all the other dates for your validation list.
 

Rkootknir

Expert Member
Joined
Dec 8, 2005
Messages
1,090
#3
Quick question to you excel fundis as Google let me down.

I have column of calculated dates which range from current to plus minus 5 years to the future. When using data validation all the dates come up. From which only 10 dates would be relevant at any given time. As time goes on the later dates would be relevant but scrolling down would be a pain in the ass.

Is there any way to limit the dates to later than a specific date and maybe the next10 max 20 entries after the date.

In other words a formula which look at a date (date x) on one sheet (Sheet a), then select from a specific column in Sheet b the 1st date after this date (date x) as well as the following 9-11 entries. These dates would then populated in column in sheet c.

I would then use data validation (list) to this column of dates.
Presumably you are using a "List" data validation control. So, for example to validate input in cell A1 when your date list is in column B (from row 1) just set the "Source" field to something similar to this:

=OFFSET(B1,MATCH(TODAY()+1,B:B,0),0,10) for the next ten dates
=OFFSET(B1,MATCH(TODAY()+1,B:B,0),0,20) for the next twenty dates

You should be able to adapt this for your situation.
 

KT-B

Honorary Master
Joined
Feb 3, 2014
Messages
10,849
#4
OP I would love to see the solution when you have it. I had not even considered the above.
 
Top