Dependant drop-down lists allowing invalid entries

[Alan]

I'm using a drop down list dependant on a previous drop down list. The first
list restricts entries to the data list, and disallows any other entry. The
second list however, provides the relevant options based on the first list,
but also allows me to input anything (ljafsdlkjfd for example) without giving
an error message. Am I missing something or is this a limitation of the excel
software or the code that's being used? I do have the "stop" option ticked
for the validation in the dependant dropdown list.

Any suggestions appreciated.

Cheers,

Alan.

 

[Dave Peterson]

Do you include empty cells in the range for the second list?

If yes, try avoiding those empty cells.

Without knowing the details, maybe using a dynamic range name that grows and
contracts with the data would help.

Debra Dalgleish explains dynamic range names here:
http://contextures.com/xlNames01.html#Dynamic

 

[Alan]

Thanks for the response. No mate, no empty cells. The formula I'm using for
the dependant list validation is as follows:
=OFFSET(D1Start,MATCH(D8,D1Column,0)-1,1,COUNTIF(D1Column,D8),1)

Any other ideas?

Also, while I'm here, I've been trying to create a three layered dependant
dropdown list - that is the first list is dependant on a range, the second is
dependant on the first value in the drop down box and the third is dependant
on the value in the second box. I'm having a lot of trouble. Any advice on
whether this is actually possible with excel would be appreciated - I'm
assuming that the technique is similar to creating a single dependant list,
but I'm just struggling with getting it to work as I intend. Any thoughts on
this?

 

[Dave Peterson]

After you choose a value from the first dropdown, hit F5 (or ctrl-g or
edit|goto) and type the name for the list of the second dropdown.

Does that range actually match what you want--and you're sure that there's no
empty cells in that range.

It's possible for more than 1 dependent dropdown.

I know when I'm working with this kind of thing, I'll use the edit|goto stuff to
make sure my names are correct. And I'll save my (failing) formulas in a cell,
too. It's just too difficult to edit in that Insert|Name|define box.