data validation, dynamic ranges

[Rich Hayes]

can you help please?

i have the following

cell C3 = data validation from a fixed range (5 drop down options)
cell C6 = data validation drop down - the choice in C3 determines the list
in drop down in cell C6 (i.e. 1 of 5 different list will appear in the drop
down)

the 5 lists which are determined by cell C3 are all dynamic ranges named,
lets call them apple, pear,orange, grape and plum.

these 5 named ranges are the same names as the drop down options in cell C3.

For some reason it doesn't work using indirect because of the formula, is
there a way to make this work?

 

[Debra Dalgleish]

There are instructions at the bottom of this page, for using dynamic
lists as the source:

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

 

[Rich Hayes]

Hi Debra

Thanks for the link, I've followed your instructions,

the first data validation list is a dynamic range, i've defined names for
all lists in the second drop down as per your instruction sheet.

it all works apart from i am still showing blank cells in the bottom of the
2nd data validation drop down for each list. Do you know how i can get rid of
these as in your example it doesn't seem to occur yet my named ranges etc are
all as per your example. I don't understand how this can happen

any help much appreciated.

Regards

Richard

 

[Debra Dalgleish]

Perhaps there are other items in the column that contains the second list.
Or, there may be cells that look blank, but which contain space characters.