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 he

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" wrote in message
...
You could use a dynamic range, that would automatically expand to include
new entries. There are instructions he

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