Data validation list using filtered range

[michaelberrier]

I have a sheet that populates a data validation in-cell drop down with
a named range of dates. This list of dates is quite long and
historical, but I only need to be able to see the last 14 days in the
drop down list. How can I set it up to filter this range in the drop
down?

Thanks,
mb

 

[Max]

Assume your named range is MyR
In Data Validation, Allow: List,
use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)

 

[michaelberrier]

Assume your named range is MyR
In Data Validation, Allow: List,
use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)

Thanks for looking, but this formula just puts the text of the formula
in the drop down box, not the actual filtered range.

 

[Gord Dibben]

Works for me.

How are you entering the formula into the List Source?

I just used CTRL + C then CTRL + V

Don't forget the "=" sign.


Gord Dibben MS Excel MVP