Using Named Ranges with Data validation

[Kevin]

Hello,

I have a worksheet where I use (or try to use) data validation from
lists in several places. For some, but not all, of them I want to
use the "Show error alert after invalid data is entered" option.
I have found that that Error Alerts work fine if my list is defined
explicitly ("Yes,No,Maybe") or by cell references ("A1:A100").
However, if my data validation list is from a named range, it seems
that the Error Alert is not shown when invalid text is entered.

Any ideas how I can get the
"Show error alert after invalid data is entered"
option to be honored when using named ranges as data validation lists?

In case it may be relevant, I am using Excel 2000 and my named ranges
are "dynamic ranges" created using Debra Dalgleish's technique at
http://www.contextures.com/xlNames01.html

 

[Earl Kiosterud]

Kevin,

This can happen if there are missing items in the dynamic range. It depends
on having no empty cells along the way. If so, you can regain your error
stop message by unchecking "Ignore blank," but some of the items in the list
won't appear in the dropdown, and will cause an error message (from the
bottom, depending on how many empty cells along the way).

 

[Kevin]

Earl,

Thanks for your help.
The dynamic range definition:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
depends on the list starting in row 1.
My list started in row 3, so I had the range defined as:
=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A),1)
but the COUNTA portion resulted in 2 extra blank rows being
added to the dynamic range. To fix it, I subtracted 2 from
the COUNTA result, as follows:

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A:$A)-2,1)

Thanks again,
Kevin

 

[Earl Kiosterud]

Kevin,

This is good as long as A1 and A2 are not empty. If someone comes along
later (could be you!)and clears either, it will have the unforeseen effect
of messing up your name definition. You could also use:

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$65536),1)

This is a bit sloppy, as it puts in a hard number, 65536. But as long as
Excel doesn't increase the available rows from 65536, and you don't actually
use them, you'll not have trouble. You'll have been promoted by then, so
not to worry.