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)

On Mar 2, 1:41*am, michaelberrier <michaelberr...@gmail.com> wrote:
> 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

On Mar 1, 2:24*pm, Max <demecha...@yahoo.com> wrote:
> Assume your named range is MyR
> In Data Validation, Allow: List,
> use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)
>
> On Mar 2, 1:41*am, michaelberrier <michaelberr...@gmail.com> wrote:
>
>
>
>
>
>
>
> > 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

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

On Tue, 1 Mar 2011 15:30:58 -0800 (PST), michaelberrier
<(E-Mail Removed)> wrote:

>On Mar 1, 2:24*pm, Max <demecha...@yahoo.com> wrote:
>> Assume your named range is MyR
>> In Data Validation, Allow: List,
>> use this as the Source: =OFFSET(MyR,COUNTA(MyR)-1,,-14)
>>
>> On Mar 2, 1:41*am, michaelberrier <michaelberr...@gmail.com> wrote:
>>
>>
>>
>>
>>
>>
>>
>> > 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
>
>Thanks for looking, but this formula just puts the text of the formula
>in the drop down box, not the actual filtered range.