Dynamic Range for Data Validation

Hi all,

I have a worksheet that uses data validation to allow users to pick from
lists of values. The lists are on another worksheet and the data validation
references these lists by named ranges.

I need to allow people to add or subtract values from these lists and have
these changes show up in the data validation pull-down menus. I don't want
to simply reference a very long column range because blank values will show
in the data validation pull-down.

I first tried creating a VBA function that adjusts the size of the named
range any time something is added or removed. But apparently a function
cannot change a named range. I don't want to use a subroutine that requires
the user to execute a macro.

Any options?

Thanks,

-- Rev

You could use a dynamic range, that would automatically expand to
include new entries. There are instructions here:

http://www.contextures.com/xlNames01.html

Revolvr wrote:
> Hi all,
>
> I have a worksheet that uses data validation to allow users to pick from
> lists of values. The lists are on another worksheet and the data validation
> references these lists by named ranges.
>
> I need to allow people to add or subtract values from these lists and have
> these changes show up in the data validation pull-down menus. I don't want
> to simply reference a very long column range because blank values will show
> in the data validation pull-down.
>
> I first tried creating a VBA function that adjusts the size of the named
> range any time something is added or removed. But apparently a function
> cannot change a named range. I don't want to use a subroutine that requires
> the user to execute a macro.
>
> Any options?
>
> Thanks,
>
> -- Rev
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Excellent - just what I needed. Thanks.

-- Rev

"Debra Dalgleish" <[email protected]> wrote in message
news:[email protected]...
> You could use a dynamic range, that would automatically expand to include
> new entries. There are instructions here:
>
> http://www.contextures.com/xlNames01.html
>
> Revolvr wrote:
>> Hi all,
>>
>> I have a worksheet that uses data validation to allow users to pick from
>> lists of values. The lists are on another worksheet and the data
>> validation references these lists by named ranges.
>>
>> I need to allow people to add or subtract values from these lists and
>> have these changes show up in the data validation pull-down menus. I
>> don't want to simply reference a very long column range because blank
>> values will show in the data validation pull-down.
>>
>> I first tried creating a VBA function that adjusts the size of the named
>> range any time something is added or removed. But apparently a function
>> cannot change a named range. I don't want to use a subroutine that
>> requires the user to execute a macro.
>>
>> Any options?
>>
>> Thanks,
>>
>> -- Rev
>>
>>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>