Name of cell range not accepted in Conditional Validation

[Guest]

When I name a range of cells by Insert>Name>Create>Top Row and the cell value
in the top row is: "Wrenches, 1/2" square" etc., the name is automatically
changed to "wrenched_1_2__square" and so on. It is not identical to the cell
/ column heading. When I then use Validation>List>=Wrenches, 1/2" square, the
list can not be found. When I rename the cell with no spaces or odd signs, it
works.

Is there a way around the renaming? I would like to keep the signs like / &
" # in the cell range name.

 

[Guest]

The / and # symbols as well as blank are reserved symbols that excel uses
internally the only way to get around it is to change your naming convention

 

[Chip Pearson]

A defined name cannot have spaces and most punctuation characters. This
prevents ambiguity in names. For example, suppose you have a name
"ABC" and another name "DEF". If Excel allowed spaces in names, you could
have a name of "ABC DEF". However, Excel uses the space character as the
Intersect operator, so the formula

=ABC DEF
is ambiguous. Does it mean the intersection of range ABC with range DEF, or
does it mean the name "ABC DEF"? Who knows?

In general, you should use only letters, numbers, and underscores in defined
names.

 

[Guest]

zzxxcc


As far as my knowledge of named ranges goes, what you are attempting to do
,is extremely difficult at best ,assuming that it's even possible.

There are strict rules as to what constitutes a "name" in Excel;some of
which are :

- they can't contain spaces
- they can't start with numbers or spaces
- to have "spaces" in between words,you MUST use an underscore - "_".

In relation to use of signs like "/" , "#" and "&" these are usually
prohibited ,especially when working in a VBA environment.

I suggest that you use easier names - a good naming convention is to
capitalize each letter of a new word and join them all together , e.g "john
doe" would become "JohnDoe".

Hope my suggesstion helps.

Regards

SysAccountant

 

[Dave Peterson]

Nope. Names can't contain those characters.

Maybe you can use Debra Dalgleish's technique:
http://contextures.com/xlDataVal02.html#TwoWord

She only cleans up space characters, but maybe you could modify it for your
names.