Dynamic Range for Data Validation

[Revolvr]

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]

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]

Excellent - just what I needed. 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