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?

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 wrote:

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

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 wrote:
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 wrote:

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

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).

S Davis wrote:

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 wrote:
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 wrote:

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

--

Dave Peterson