Name Ranges error

[bluegrassstateworker]

I have two name ranges that are not duplicates but produce an error
message when I try to create them using either one of the names
below. There are no spaces, just underlines in the attempted named
range:
C100_Division_of_Program_Integrity
2000_Division_of_Protection_and_Permanency

If I take the range and name it "test" or anything similar and
shorter, then Excel accepts the name and creates the named range.
What is strange is that I have a similar named range:
A200_Office_of_Legislative_and_Public_Affairs that Excel took without
an error message.

The C100_Division...... name is not listed elsewhere. I have
installed the name manager add-in and it is not listed. I do have a
lot of named ranges but why will it take the shorter "test" but not
the longer name (with underlines, no spaces) ?? I am using Excel 2003.

 

[JE McGimpsey]

I suspect this is a bug.

In brief testing, if the first 1 to 5 characters before the underscore
can be interpreted as part of an RC address (e.g., C1_, C100_, R35_,
R1000_) then the name is rejected.

 

[bluegrassstateworker]

I suspect this is a bug.

In brief testing, if the first 1 to 5 characters before the underscore
can be interpreted as part of an RC address (e.g., C1_, C100_, R35_,
R1000_) then the name is rejected.





- Show quoted text -

This is certainly a bug. What is strange though is that I have a
similar name created range called:
A200_Office_of_Legislative_and_Public_Affairs that is very similar to
the one rejected by Excel.

 

[JE McGimpsey]

Not so strange if my supposition is correct about RC addresses. An
A1-style address wouldn't trigger the bug.

 

[bluegrassstateworker]

Not so strange if my supposition is correct about RC addresses. An
A1-style address wouldn't trigger the bug.



- Show quoted text -

Here are name ranges Excel <rejected>:
C100_Division_of_Program_Integrity
2000_Division_of_Protection_and_Permanency
3000_Division_of_Family_Support
4000_Division_of_Child_Support
6000_Division_of_Child_Care


Here are some it <accepted>:
B000_Division_of_Staff_Resource_Development
G100_Division_of_Quality_Living
C000_Division_of_Service_Regions
W400_Div_Long_Term_Care_and_Comm_Altern
W500_Division_of_Hospitals_and_Provider_Operations

If I add: an preceeding underscore
(_C100_Division_of_Program_Integrity) then Excel accepts it. Any
workarounds to this bug?


Perhaps I am missing where these as a group of rejected and accepted
are dissimilar.

 

[Peo Sjoblom]

It won't accept any name that start with a number by design (I believe) and
the first (C100_etc) it rejected because of the RC bug



--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

Any workarounds? Well, you proposed one already (leading underscore).

RC addresses begin with "R" or "C" so your "B...","G...","W...." names
are irrelevant if my supposition is correct.

"C000_" doesn't start with a valid RC address, either, since there's no
column 0. And names can't start with a number (see Help, "About labels
and names in formulas").

C100_, OTOH, does start with a valid RC address (column 100).

My supposition is, I think, a bit stronger as

C256_abc

which starts with a valid column reference, is rejected, while

C257_abc

which starts with an invalid (pre-XL07) column reference is accepted.
Likewise the valid row reference starting

R65536_abc

is rejected but the invalid (pre-XL07) row reference beginning

R65537_abc

is not.

 

[bluegrassstateworker]

Any workarounds? Well, you proposed one already (leading underscore).

RC addresses begin with "R" or "C" so your "B...","G...","W...." names
are irrelevant if my supposition is correct.

"C000_" doesn't start with a valid RC address, either, since there's no
column 0. And names can't start with a number (see Help, "About labels
and names in formulas").

C100_, OTOH, does start with a valid RC address (column 100).

My supposition is, I think, a bit stronger as

C256_abc

which starts with a valid column reference, is rejected, while

C257_abc

which starts with an invalid (pre-XL07) column reference is accepted.
Likewise the valid row reference starting

R65536_abc

is rejected but the invalid (pre-XL07) row reference beginning

R65537_abc

is not.






- Show quoted text -

Thanks JE and Peo for your thoughts and input! Looks like I have a
challenge to resolve...