Invalid Name Range

[Guest]

I am wondering if Microsoft, in their infinite wisdom, came up with some
alghorithm that determines if a named range is valid or not.
I am trying to systematically create range names based on equipment names in
our system. One of which is "BS4".
My macro will create the named ranges fine but it appears to be an invalid
name that Excel does not like. I have also seen that "HF4" is also a named
range Excel does not like.

Can anyone tell me if there is a reason for this and a way around it?

My macro is building 3 tiers of named ranges to use in 3 dropdown boxes.
The 1st dropdown has the parent equipment, 2nd has the sub parent equipment,
the 3rd has the child equipment of the sub parent. ALL of the named ranges
are unique.
The 1st dropdown selects the parent equipment of which each is a named
range. The 2nd dropdown box reads the Named range in the 1st box and using
Indirect reads the related list I want.
The 3rd dropdown reads the value in the 2nd dropdown as a named range and
selects the related list. However, "some" named ranges do not show anything
in the list. When I check the named range, it is one of those that Excel
does not like.

Any assistance is most appreciated !

 

[meatshield]

I imagine the problem is that there already is a "BS4", and "HF4". It
refers to the cells in row 4 of Columns BS and HF respectfully.
Depending on the version of excel you are using, you might have to use
named ranges that are different than the current column and row names.
I hope that helps, let me know if you have any more questions.

 

[Guest]

Thank you so much. That makes sense and I did not even think of it.
--
Edwin Kelly
Houston, TX

I imagine the problem is that there already is a "BS4", and "HF4". It
refers to the cells in row 4 of Columns BS and HF respectfully.
Depending on the version of excel you are using, you might have to use
named ranges that are different than the current column and row names.
I hope that helps, let me know if you have any more questions.

 

[Dave Peterson]

And if you're ever going to upgrade to xl2007, there are 16k columns--which
means that the columns now use 3 characters.

You may want to use a name like:
_BS4
and
_AAA4

To avoid any potential problems.

Thank you so much. That makes sense and I did not even think of it.

 

[Guest]

Thanks for the tip! That will be useful in the future.