URGENT: Trying to ALLOW Special Characters When Using Data Validation in excel

[gmaboing]

I've seen lots of help on how to disallow special characters when using data validation but nothing on how to ALLOW special characters.

I have a pretty big spreadsheet that I'm working on that will be pushing the limits of data validation . I'm really trying my best not to change the names in the cells too much so that I don't jepordize the data's integrity.

This will be a spreadsheet for 'end-users' and I don't want them to (for lack of a better word) 'freak out' when they don't see things spelled exactly like they are used to seeing them. (You can only add so many underscores and delete so many spaces before you start to loose some folks.)

Anyone have any ideas? Here are a few examples of how I would like the cells to look:

Atlantic Southern (Health) (Ir)
NY A&H Spec Risk - Accident
John Hancock Fac-Ob*

Please help. Thanks!

 

[pgc01]

Hi

I cannot understand the question.

What do you mean by allow special characters. If you mean display a list of strings like the one you posted to the user, there's no problem with that in Data Validation.

Place the strings in some range and then use the range address in the Data Validation list.

 

[gmaboing]

When I try to name a cell a name that contains a space or parenthesis, etc., I get an error message stating "You must enter a valid reference you want to go to, or type a valid name for the selection."

For example, I am trying to name a cell "Atlantic Southern (Health)". After I try to name the cell, I get the error message.

If I take out the spaces and parenthesis, etc., the cell will accept the name I assign to it.

 

[Jonmo1]

Sorry, there's no getting around that rule...
And it's not Data Validation that doesn't like those characters, it's Names (insert - name - define).
Certain characters just are not allowed in Names. Period.


The only possible workaround I can see is to use substitute..

If A1 = Atlantic Southern (Health) (Ir)
And it should refer to say B1:B10
Higlight B1:B10
Go to Insert - Name - Define.
Call it AtlanticSouthernHealthIR

Then in your data validation, use this
=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"(",""),")",""))

This takes A1, and removes all the spaces and parens.


That's very tedious, and probably not worth all the effort to remove ALL the disallowed characters.

 

[gmaboing]

Actually, it looks like I found my answer...

"A Defined Name must begin with a letter or an underscore ( _ ) and consist of only letters, numbers, or underscores. Spaces are not permitted in a Defined Name. Moreover, a Defined Name may not be the same as a valid cell reference. For example, the name AB11 is invalid because AB11 is a valid cell reference. Names are not case sensitive."

http://www.cpearson.com/EXCEL/DefinedNames.aspx


Well, that stinks...

Unless someone knows how to cheat and get around these rules without my putting my pc in 'paperweight mode', then I'm...

AARRRGHHH!!!!

 

[gmaboing]

Hi Jonmo1,

Yes, I have done the renaming in other places... the problem is the spreadsheet is so big and there are so many options that need to be done that at this point I'm really going to have to butcher the names. The spreadsheet is for the end users and I want to try and eliminate as much confusion and head scratching as possible.

But the good thing is that it's not mandatory, I was just trying to head off as many questions as possible and basically 'spoon-feed' the information to the users.

Thanks for the replies and brainstorming on this... this is one of the best discussion boards I've ever used!

 

[doofusboy]

There simply is NO way to code around

are not permitted in a Defined Name

 

[gmaboing]

Thanks doofusboy and everyone