Limit to number of named ranges before Excel starts messing things up?

[S Davis]

Is there a limit to the number of named ranges? I am running into
errors now with my large (large) workbook here, which contains a ton of
named ranges just to make things easier for me.

For instance, a simple formula such as this:

=COUNTA(RBCJobTitle06)-COUNTIF(RBCJobTitle06,"")

.... which returns "5" on the sheet in which the named range resides in,
returns 17 on another worksheet, and a #REF! error on still another
worksheet.

Is my workbook messed up?

 

[Dave Peterson]

Maybe you have a workbook level name and multiple worksheet level names that all
share the same name.

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

It'll make it much easier to verify your names.

 

[S Davis]

That would make sense. Im guessing that the two sheets that returned
different values (5 and 17) are the ones that share the same name.
Other sheets would then return a #REF! error as they wouldn't know
WHICH name I was actually meaning.

Ill take a look at that download, sounds handy

(Just verified the existence of the same name on two worksheets... )

 

[Dave Peterson]

If you have a workbook level name, then the worksheets without that same
worksheet level name will get the value from the correct worksheet--using the
workbook level name.

I was guessing that maybe the worksheets that returned #ref! errors actually had
worksheet level names--but were broken.

For instance:
Name A1 Sheet99!Test
Use that name in another cell on that same sheet99.
=test
You'll see the value nicely.

Delete column A and you'll have the #ref! error.

And if you do Insert|Name|Define, you'll see that that sheet level name now
refers to:
=Sheet99!#REF!

But you'll be much happier with all the things you can do with names using that
addin from Jan Karel (et al).