Data validation list with date condition

[benlw1984]

Greetings Everyone,

I regret to be one whose first post after joining a forum is to ask for help sorting out a problem, but alas, I'm stumped. I hope I can repay any assistance in kind in future.

I've got a data validation list (G2) which is sourced from a named range (list_rp) of roster periods that are a concatenation of a start date and an end date - e.g 20/07/2019-16/08/2019.

At the moment, the applicable roster period is near the top of the list. However, as the list is quite extensive, as time goes by the user is going to have to scroll through the list quite some way to find the current roster period - a source of great frustration for some, apparently. So, I'd like to incorporate a condition in the data validation list or its source data to exclude roster periods in the past, with the exception of the last three (prior to now). For example, assuming I was using the drop down list today and today is 2/1/2020, I'd like the data validation list to exclude the previous roster periods of 20/07/2019-16/08/2019 & 17/08/2019-13/09/2019 and commence the list with 14/09/2019-11/10/2019 and those that follow.

I've attached an example workbook which probably demonstrates things a bit better. I'd appreciate any suggestions!

Thank you in advance.

Regards

Ben

[AliGW]

I regret to be one who's first post after joining a forum is to ask for help sorting out a problem, ...

Well, as it's a help forum, I regret to have to inform you that you aren't the first, in fact practically all members start with a plea for help.

Welcome to the forum - I shall have a look at your workbook presently.

[AliGW]

Somewhere else in the workbook (maybe on a hidden sheet) type this:

=INDEX(list_rp,MATCH(TODAY(),$B$2:$B$85,1)-ROWS(A1:A$3))

Drag copy down three rows.

Now use this as your validation list reference.

[Glenn Kennedy]

Use this as the DV formula in the named range:

INDEX($A:$A,MATCH(TODAY(),$B:$B,1)-3):INDEX($A:$A,MATCH("zzz",$A:$A))

[benlw1984]

Many thanks for the replies - problem solved!

Regards

Ben

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.