Named Ranges -- Link Issue?

  • Thread starter Thread starter zodiac711
  • Start date Start date
Z

zodiac711

I am working on a very large Excel workbook (231 worksheets, all inter-linked
to one another). Each worksheet contains a number of named ranges (for the
entire workbook, there are 6,117 named ranges).

This appears to be causing a problem such that if I modify a cell on
Worksheet1, and Worksheet2 is dependent on the value, Worksheet2 does NOT
change UNLESS I rename Worksheet1. (I can rename it to _Worksheet1 and then
back to Worksheet1, but again, unless I actually *change* the name, the link
is not updated).

Calculation is set to automatic (but even if I force calculation, be it on
the worksheet, or the entire workbook), it does NOT update the values.

I wrote a macro to wipe-out all the named ranges. Upon doing this, the
issue *appears* to have gone-away.

My questions are two-fold:
1) Has anyone ever experienced something like this?

2) Is there a way I can [easily] determine if any of the named ranges are
actually being used? I know I can manually search, but with 6,117 named
ranges (yes, some are duplicate names by worksheet, so it isn't AS MANY to
search, but still is a lot). I suspect that NONE of these are actually
in-use, but before I wipe them all out, I'd like to confirm that to be the
case.

I am using Excel 2003 on a Windows XP SP3 machine, all patches are up-to-date.


Thank-you for any/all help that you can provide.
 
Charles,

Thank-you for the link. I thought I remembered a program similar (if not
this exact oen), but could not find it.

Thanks again


Charles Williams said:
Download Name manager from
http://www.decisionmodels.com/downloads.htm

and then you can either filter for Unused names (which will be extremely
slow for your large workbook), or check where an individual name is being
used.

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

zodiac711 said:
I am working on a very large Excel workbook (231 worksheets, all
inter-linked
to one another). Each worksheet contains a number of named ranges (for
the
entire workbook, there are 6,117 named ranges).

This appears to be causing a problem such that if I modify a cell on
Worksheet1, and Worksheet2 is dependent on the value, Worksheet2 does NOT
change UNLESS I rename Worksheet1. (I can rename it to _Worksheet1 and
then
back to Worksheet1, but again, unless I actually *change* the name, the
link
is not updated).

Calculation is set to automatic (but even if I force calculation, be it on
the worksheet, or the entire workbook), it does NOT update the values.

I wrote a macro to wipe-out all the named ranges. Upon doing this, the
issue *appears* to have gone-away.

My questions are two-fold:
1) Has anyone ever experienced something like this?

2) Is there a way I can [easily] determine if any of the named ranges are
actually being used? I know I can manually search, but with 6,117 named
ranges (yes, some are duplicate names by worksheet, so it isn't AS MANY to
search, but still is a lot). I suspect that NONE of these are actually
in-use, but before I wipe them all out, I'd like to confirm that to be the
case.

I am using Excel 2003 on a Windows XP SP3 machine, all patches are
up-to-date.


Thank-you for any/all help that you can provide.
Click to expand...
Click to expand...
 
Back
Top