Excel: Data Validation

[rh1]

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.

 

[TribbleZA]

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]

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.

 

[TribbleZA]

OP I would love to see the solution when you have it. I had not even considered the above.

 

[rh1]

Thanks. Will attempt tomorrow and provide feedback.