Dependent Data Validation question

I have a list of dates in chronological order and is a dynamic range called "Dates". I want to have two data validation dropdowns, for a starting date and an ending date. My question is - how can I ensure the validation list for the ending date are only those dates AFTER the date selected in the Starting Date validation dropdown?

Dynamic named ranges using OFFSET is usually the easiest way to accomplish this.

Assuming
1) on Sheet1 you have a list of dates in column I start in I1 and going downward any length
2) In B2 you will use a drop down to select a starting date from that list
3) In B3 you want a second drop down to ONLY show the remaining dates in the list

you create two dynamic named ranged.

1) Open the Name Manager (Ctrl+F3)
2) New > Name = AllDates > RefersTo: =OFFSET(Sheet1!$I$1, , , COUNT(Sheet1!$I:$I), )
3) New > Name = RemainingDates > RefersTo: =IF(Sheet1!$B$2>0, OFFSET(AllDates,MATCH(Sheet1!$B$2,AllDates,0),,COUNT(AllDates)-MATCH(Sheet1!$B$2,AllDates,0),))

4) Close the Name Manager
5) Select B2 and use the Data Validation settings: Allow = List > Source = AllDates
6) Select B3 and use the Data Validation settings: Allow = List > Source = RemainingDates

That should do it.


TO apply this in a different layout, make note of AllDates and how it refers to the first cell of the list dates, then refers that entire column. Edit that.
Then note how RemainingDates refers to the CELL where you are going to use the AllDates, in my example that is B2. Edit that.

Hello,

you can do that with another dynamic range name for end dates

Please Login or Register  to view this content.

See the screenshot
2018-05-30_16-45-19.jpg

cheers, teylyn

Great guys - Thank you both!