Solved: prevent typing in a Data Val drop-down

[Gingertrees]

Hello,
I have a workbook where client ID, name, address, etc is on one sheet (Cases) and the user can select the client's ID at the top of other sheets (via a drop-down with data validation for a named range) to populate the other sheets.

This works great so long as the user sticks to the list. Unfortunately, users are circumventing this by typing client IDs in the drop down. This too is ok, except for the fact that people make mistakes and type client IDs that don't exist, thus telling the computer to reference data that returns the beloved N/A# error.

E.g.: say clients are Tom, Dick and Sally. If you type "Sally" in the drop-down on another sheet, no problem. If you type "Sully" instead, her address, date of birth, etc will all appear as N/A#. HOW DO I PREVENT THIS ANNOYING OCCURENCE? Thank you for any help. I've attached an example with cartoon characters for demonstration.

[Simon Lloyd]

If you name your range, call it MyRange, then in the data validation choose allow LIST and type =MyRange hit enter, now when they type something that is not on the list they get an error warning!

[Gingertrees]

Simon,
I've already done that - No error message. Perhaps this is a version issue; I'm working with Excel 2003. Any other ideas?

[Simon Lloyd]

It must be something you have done as it's not version specific....are you sure you are using data validation and not a dropdown from the toolbox?

Take a look at the attached.

[Simon Lloyd]

I spotted your problem you had the invalid data error turned off!, click on the cell then data validation,then Error Alert and check the "Show error alert after invalid data is entered" checkbox

[Gingertrees]

I noticed that after your first post - checked it, but it still doesn't show an error.

I think the problem is that my named range includes so much open space. For example, I noticed the same problem on the location cell in the Cases tab, and that was fixed by checking the error checkbox. Location however is a finite list of 4 possibilities - not the 200 for Names. But, that's necessary to allow many clients to be listed.

Or perhaps all the background code is interfering? I'm lost...

[Simon Lloyd]

No you are right, if you have spaces in your named range (because you built them in just in case the list grows) then naturally anything you enter in the validation cell will be correct!, you should create a dynamic named range, go to Debra Dalgliesh' site here http://www.contextures.com/xlNames01.html and create a dynamic named range, this range will then expand and contract depending on how many cells have data.

[Gingertrees]

That's a cool tool...but even with a dynamic named range, if the user leaves rows open (no I wasn't smart enough to do a userform...and am regretting it), they still have the possibility to type weird data into the drop down and screw things up.

Isn't there some "is not blank, then error if not in list" routine that I can add? Sorry to be insatiable over here...

[GTO]

Greetings,

How did you redefine 'Names'? As it looks like there is currently a limit (A5:A204) for how big the list could be, try this:

In the 'Define Name' dialogm in the 'Refers To:' box for 'Names':

"=OFFSET(Cases!$A$5,0,0,COUNTA(Cases!$A$5:$A$204),1)"

W/O the quotes...

With the above formula defining the named range, the validation seems to work fine to me.

Does that help?

Mark

[Gingertrees]

ok, question has changed enough I'm going to end this thread.