Data validation list using filtered range

  • Thread starter Thread starter michaelberrier
  • Start date Start date
M

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
 
Assume your named range is MyR
In Data Validation, Allow: List,
use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)
 
Assume your named range is MyR
In Data Validation, Allow: List,
use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)
Click to expand...

Thanks for looking, but this formula just puts the text of the formula
in the drop down box, not the actual filtered range.
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combining two non-contiguous lists into one for data validation, with one range being dynamic 0
Filtered drop down list 2
Filter a Drop List ? 1
Filling Out Cells Based on Data Validation 1
Adding to a data validation list 2
Data Validation - List of unique values 2
Validation list in data entry form 4
Data Validation and Dynamic Ranges 2

Back
Top