Dependent data validation with dynamic named ranges

[Ace_XL]

This seems to be rather annoying.

I am unable to use dependent data validation lists using the INDIRECT function when the initial named range is a dynamic one.

See attached.

The named ranges 'Men','Women','Children' are all dynamic based on number of entries in each column order to accommodate a growing list, whilst also not having blanks in the dropdown (hence I haven't used entire column ranges). The named range 'test' is a static one.

Column H has a dependent data validation based on entry in Col G. This works for the static list, but not the dynamic one!

Any workarounds? preferably no VBA

[sweep]

Hi,

There's an article on this at http://www.contextures.com/xlDataVal02.html under "Using Dynamic Lists"

[MarvinP]

Hi Ace,

Isn't this just Cascading Dropdowns? See
http://www.bluepecantraining.com/por...ists-in-excel/

[Bob Phillips]

Don't use INDIRECT

=CHOOSE(MATCH(G2,B1:D1,0),Men,Women,Children)

[Ace_XL]

@ sweep: thanks for the link. I've frequented this section of Debra Dalgleish's site and though it deals with the dynamic dependent lists it uses a roundabout approach and extra named ranges to achieve that

@Marvin: yes, these are cascading drop downs, but with dynamic named ranges (not static ones)

@ Bob: The CHOOSE option is handy and I'll explore the possibilities of using that, although with many dynamic ranges it'd be cumbersome. Does seem like the best option under the circumstances.

Thanks all for responding