# Data validation - drop down list - latest date

1. ## Data validation - drop down list - latest date

Hey all,

I created a range "dates" with an x-amount of rows.

I would like to offer 2 drop down lists (via data validation):

Start date
=dates

End date.
The end date should be >= start date AND <= latest date in the range "dates".

=AND(B5>=B4; B5<=MAX(dates))

This formula works, but only when you select CUSTOM in the data validation.
It doesn't work when you chose LIST.

Can any1 help me?

2. ## Re: Data validation - drop down list - latest date

I'd use two named ranges, like this. DV/formulae in shaded cells. Happy to explain if this meets you needs.

3. ## Re: Data validation - drop down list - latest date

Hey Glenn,

Isn't there a solution without having to make an extra column/range? I don't have much "room" for this in my current assignment.
Can the IFERROR function be integrated in the list formula?

Google doesn't help much although this should be a pretty common question.

4. ## Re: Data validation - drop down list - latest date

Alternate method.

Dynamic named range formula for end date.
Assuming dates in Column A and start date in G2.
=OFFSET(Sheet1!\$A\$2,MATCH(Sheet1!G2,dates,0),,ROWS(dates)-MATCH(Sheet1!G2,dates,0))

5. ## Re: Data validation - drop down list - latest date

Very neat CK76. One tweak. Use a dynamic formula for the start dates:

=Sheet1!\$A\$2:INDEX(Sheet1!\$A\$2:\$A\$1000,COUNTA(Sheet1!\$A\$2:\$A\$1000))

6. ## Re: Data validation - drop down list - latest date

Hey CK76,

Where exacly do you put the OFFSET formula. I can't find this in your sheet, nor the end_date range that you used in the data validation list.
Did you only create this in the Name Manager?

(I've never used OFFSET before).

Bram

7. ## Re: Data validation - drop down list - latest date

Go to formula ribbon tool and find name manager. Named range is there. Formula is used in the named range.

As Glenn suggested, use dynamic named range for "dates" as well.

0.JPG

8. ## Re: Data validation - drop down list - latest date

I guess that your solution is similar to this:

https://www.excel-university.com/dat...ion-drop-down/

I'll see if I can get it to work. Getting a headache now :p

Big it up for both of you!

9. ## Re: Data validation - drop down list - latest date

Hi guys,

A small question for a beter understanding.
How do I make sure that the end date can be the same as the start date?

The end date is always atleast 1 day after the start date.

10. ## Re: Data validation - drop down list - latest date

So it should be >start date?

In that case just add 1 to start date.

I.E. MATCH(Sheet1!G2+1,dates,0)

11. ## Re: Data validation - drop down list - latest date

Hey CK76,

Mmm, I can't get this +1 thing to work.
When I do +1, the end date is atleast +2 days ahead of the start date.
When I do -1, the drop down list freezes.

Could you or Glenn Kenney have a look?

(I added test.xlsx to the attachments. The info is in the worksheet called 'Summary'.)
(Sorry, still learning a lot.)

Best regards,

Bram

12. ## Re: Data validation - drop down list - latest date

Correction: I get freeze problem too!

13. ## Re: Data validation - drop down list - latest date

Try

for "End_date"

=OFFSET(Table1[Date lg.],MATCH(Summary!\$B\$4,dates,0)-1,,ROWS(dates)-MATCH(Summary!\$B\$4,dates,0)+1)

14. ## Re: Data validation - drop down list - latest date

Like this, maybe??

15. ## Re: Data validation - drop down list - latest date

I see JT has jumped in d got there while I was eating my breakfast!! Same solution.

16. ## Re: Data validation - drop down list - latest date

Thanks guys!

I knew I had to work with the 1 or +1 so I definitely learned something.

+1 rep

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1