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

gmaboing

New Member
Joined
Mar 24, 2010
Messages
33
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

New Member
Joined
Mar 24, 2010
Messages
33
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.
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Mar 24, 2010
Messages
33

ADVERTISEMENT

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...

229551714_a5b4f7bc43.jpg


AARRRGHHH!!!!
 

gmaboing

New Member
Joined
Mar 24, 2010
Messages
33
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!

t273440180_57106.gif
 

Forum statistics

Threads
1,141,413
Messages
5,706,300
Members
421,440
Latest member
cmphares

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top