| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Name of cell range not accepted in Conditional Validation
When I name a range of cells by InsertNameCreateTop 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 ValidationList=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. -- Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Name of cell range not accepted in Conditional Validation
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 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "zzxxcc" wrote: When I name a range of cells by InsertNameCreateTop 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 ValidationList=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. -- Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Name of cell range not accepted in Conditional Validation
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. "zzxxcc" wrote in message ... When I name a range of cells by InsertNameCreateTop 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 ValidationList=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. -- Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Name of cell range not accepted in Conditional Validation
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 "zzxxcc" wrote: When I name a range of cells by InsertNameCreateTop 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 ValidationList=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. -- Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Name of cell range not accepted in Conditional Validation
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. zzxxcc wrote: When I name a range of cells by InsertNameCreateTop 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 ValidationList=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. -- Thanks -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
| Names of workbook cell ranges not accepted with some signs | Excel Worksheet Functions | |||
| No. of digits accepted in one cell | Excel Worksheet Functions | |||
| Inconsistency in how Range object is accepted? | Excel Programming | |||
| Conditional data validation (using a filtered range?) | Excel Worksheet Functions | |||
Hybrid Mode
