Data Validation Error Alert not Working

[mvancleave]

I have applied data validation to certain columns based on named ranges in another sheet.

Data/Validation Select "List" Source =NamedRange

I have selected the "Show error messages ... " box and added message text, but the alert does not come up when invalid data are entered.

The ranges are defined dynamically using the offset and counta functions. When I define the names statically, everything works fine.

What can I do to fix this, short of using static name defintions?

Thanks,

Mike

 

[Von Pookie]

Works just fine for me using a dynamic named range on another sheet.

Are you certain the data being entered is invalid?
I select a value from the list with no problems, but if I enter a value not contained in the list, the error appears.

If you manually enter a value that is contained in the dynamic range, it will be accepted since the value is in the list (capitalization doesn't appear to matter).

 

[mvancleave]

I'm sure that data is invalid, and I don't get the error message. This is with Excel 2000, so maybe that makes a difference. I can check on my computer at home (Excel 2003) and see if the same problem happens there.

 

[mvancleave]

Ok, so this is interesting. Here is how I had my dynamic range defined.

First, i have a cell named RangeCount that counts the entries in the range (COUNTA = ), then my range definition refers to this named cell for the offset. If I enter the COUNTA directly in the range definition, the error message works. It must have something to do with the order in which excel is counting things. Hmm ...

 

[mvancleave]

Ok, so I went and named all of my validation lists with static names and now I can't get any stop alert messages. There is something else wrong.

Any ideas?

 

[ExcelChampion]

Posting your formulas and the data types will help us help you much faster.

Or, follow this example:-

Name:- Lrow
Formula:- =MATCH(REPT("z",255),Sheet1!$A:$A)

Name:- rng_List
Formula:- =INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!$A:$A,lRow)

Data/Validation/List/=rng_List

Note: Becareful when using COUNTA for dynamic ranges. If you have blanks in the range the dynamic formula will fail. The above method of using MATCH is my preference because it allows for blanks.

 

[mvancleave]

Well, you hit the nail on the head. I had included a blank in the range, which was the glitch. The dropdowns had the right data in them, but the no error message.

Clever way to find the last row with the REPT function! The significance of the "z" makes sense, but is there a significance to the 255 or is it just a big number to ensure enough "z" to shove it to the end of the list?

Lastly, would you consider combining the to formulas? What would be the negative ramifications?

=INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))

Thanks!

Mike

 

[ExcelChampion]

Clever way to find the last row with the REPT function! The significance of the "z" makes sense, but is there a significance to the 255 or is it just a big number to ensure enough "z" to shove it to the end of the list?

Lastly, would you consider combining the to formulas? What would be the negative ramifications?

To answer your first question, yes.

To answer your second question, probably doesn't matter. I define lRow separately because I usually use it somewhere else. I suppose it doesn't matter in your case.

FYI, I first saw this method from Aladin Akyurek.

 

[Kentetsu]

Thanks Guys, you helped a lot when I ran across this same issue. Found a blank at the end of the table being referenced. Never heard of that being an issue before, and really had me scratching my head.

Thanks again.

 

[visimg]

Thanks Guys, you helped a lot when I ran across this same issue. Found a blank at the end of the table being referenced. Never heard of that being an issue before, and really had me scratching my head.

Thanks again.

Thank you! I had the same problem with the error alert not popping up when I entered invalid data. Deleted the blank rows in my reference table and data validation warning worked once again.