Named Ranges -- Link Issue?

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.
0
zodiac711 (10)
4/14/2009 6:33:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
445 Views

Similar Articles

[PageSpeed] 55

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" <zodiac711@discussions.microsoft.com> wrote in message 
news:447FDC5C-6757-4C70-8FD5-19FF3C69C37E@microsoft.com...
>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.
> 


0
Charles740 (233)
4/14/2009 9:28:33 PM
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" wrote:

> 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" <zodiac711@discussions.microsoft.com> wrote in message 
> news:447FDC5C-6757-4C70-8FD5-19FF3C69C37E@microsoft.com...
> >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.
> > 
> 
> 
> 
0
zodiac711 (10)
4/14/2009 11:21:01 PM
Reply:

Similar Artilces:

VLOOKUP
When I query a datatable using VLOOKUP I get the answer returned fro the first instance of a name that appears albeit in the correct column If ABC is in row 2 with a value of 5 and also in row 3 with a value o 6, I get the reult 5 from row 2 in my formula. What I need is for formula that will sum all instances of the row name ABC so giving m the answer 11 (5+6). Ideally I don't want to go into VB scripting bu can handle nesting ormulae. I don't want to use dat>subtotals either a I need it to work 'behind the scenes' I am using Excel 2000. Any help would be gratefully receiv...

Outlook Name Suggestion Issue
I am working on a strange issue with name suggestion in Outlook 2k3 version. For some users when I type the alias for the first time it resolved the name. The next time I try to type few characters for the same user. it suggests me with the complete name but this time in the brackets <> the alias would be some starnge name with some numbers. This strange alias number does not existing in our organization nor the number that appears along with it appears in the User AD properties. Examples: First time I type the alias as: "nnair" and press Ctrl + K = It resolves...

insightbb and windows mail... anyone else having issues?
Since switching to insightbb I can not send emails (worked correctly the first week). I get no errors, shows the email in sent items, but no one receives them???? What happens when you send yourself an email? If it doesn't arrive in your Windows Mail inbox, does it arrive in your webmail inbox? --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "maintenancegod" <maintenancegod@discussions.microsoft.com> wrote in = message news:679D1F6E-8CD5-4C58-B2BA-ABBB2B8D3784@microsoft.com... > Since switching to ...

Can't find Edit, links dialogue box to break the link
After linking a cell between 2 worksheets, can't find edit-link to break the link created earlier. Are these two worksheets in the same workbook? If yes, then edit|link won't work. If you're having trouble finding links to other workbooks... When I can't find links, I'll use Bill Manville's FindLink program: http://www.oaltd.co.uk/MVP/Default.htm Angie L. wrote: > > After linking a cell between 2 worksheets, can't find edit-link to break the > link created earlier. -- Dave Peterson ...

Synchronization issues for Calendar items in Entourage 2004
I'm using Entourage 2004 to connect to an Exchange 2003 server at work. On my desktop, the calendar seems to work fine. But from laptops, there are problems: I made changes to a couple of calendar items, and eventually closed the laptop. I got to work, and don't see the changes reflected here. Another laptop I opened at home took one old calendar entry and used it to replace a more recent one, losing my changes! What's going on - is there a way I can track the synchronization and perhaps set some clients to be "read-only"? I hate the fact that some changes don'...

DataSet row to a Object Array Casting issue. Boolean
I have a class name OrderAckType and it has 3 variables public boolen orderSubmit; public string orderid; public string orderNumber; and i have to get the data from the database and basically this is my Query select Convert(bit,1) as orderSubmit, orderid,orderNumber from order_master The data is returned in the DataSet. Now i want to move that data to this object. so i am doing this. OrderAckType[] oack2 = new OrderAckType[10]; oOrderAckDS.Tables[0].Rows.CopyTo(oack2,0); it cannot convert this bcos of the casting, in the Dataset the ordersubmit is "true" and datatype String. ...

Publisher 2003 Printing Issue
Hi, My client has Office 2003 SP3 installed under WinXP SP2. They are having trouble with printing a newsletter from Publisher. The publication is an 8-page newsletter in "booklet" form. Each page is 8 1/2 by 11. The printer is a Konica copier with large-format (11x17) and duplex capabilities. Everything works fine when the user prints the entire booklet to the printer. They get one booklet ready to fold. One sheet of paper has pages 1, 2, 7, and 8. The "inside" sheet has pages 2, 3, 4., and 5. However, the user needs to be able to print several hundred copies of each...

Linking To Autofilter Arrows
Is it possible to select criteria from an autofilter but from different worksheet. Can dropdown arrows be linked or copied via macro? Ton -- Message posted from http://www.ExcelForum.com Here's a link to an answer you got when you posted this question a few days ago: http://groups.google.com/groups?&threadm=410AC9A2.7090406%40contexturesXSPAM.com TONYC < wrote: > Is it possible to select criteria from an autofilter but from a > different worksheet. Can dropdown arrows be linked or copied via a > macro? -- Debra Dalgleish Excel FAQ, Tips & Book List http://...

Money 2007 online issues?
I recently installed money 2007. I previously owned the 2004 version. I am trying to set up my online banking accounts thru USAA. When I go to do this, and try to chose my bank, in red text, it says "list is temporarily unavailable". When I try and just do updates, it will not let me. Also, when I try to sign in, it says that my windows live accoutn can not be verified, please go online and try again. So i'm getting the impression that money 2007 is somehow not connected to the internet. I have verizon FIOS so I know I am connected. Also, i noticed that everytime I try to ...

An issue with hyperlinks...
I am making a big document for school in Excel that involves hyperlinks to other worksheets and a menu. I work on a Mac OS X and use the Excel X. The problem is that i create a hyper link to another worksheet, everything is fine and lovely. the problem begins when i move the file to another folder or file. When that happens it just cant find the file because the link or path still links to the file's previous location. Every one else who uses excel in windows dont have any problems at all! Where can i find the option or whatever...??? -- nerdman ----------------------------------------...

Template & linked Excel database question
Okay - I got my template set up & linked to an Excel worksheet for a database. I open the template and start entering info - the first field is the client's name & has a macro that saves the file as "Name.xls" when moving out of this cell. I go on to complete the initial data entries, then save & close...but I don't get the prompt to save as a new entry to the database file UNLESS I then re-open the same file and close it again. Why don't I get the prompt the FIRST time I save & close???? tm ...

i want to only select one name from a list of the same names
I have a column of names and in the column there are 20 rows saying "apples" and 10 rows saying "oranges" and 5 rows of "bannanas". I would like to produce a report that pulls only the name i.e. "apples", which would then allow me to produce a consolidated list of names. This would mean that I would end up with 3 rows one saying "apples", one saying "oranges and one saying "bannanas". In B1 use =A1 In B2, add =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$20&""),0)),"", INDEX(IF(ISBLANK($A$1:$A$20),&qu...

2006 upgrade issues
I've upgraded from 2004 to 2006. Two issues I've noticed are 1) mangled up some categories 2) screwed up a car loan payment Regarding the categories, I used to have two "Food" cat/subcat combos) food:groceries food:dining out M06 converted these two into three categories food groceries dining out Not sure why this happened. Regarding the car loan payment, M06 forgot that the payments were being transferred into a loan account and just decided to create a new category. On the plus side, I do like the new backup feature (giving each backup a unique ...

Autofill Issues
Hi, any help gratefully recieved. If I write a formula then fill down I get the same answer in every cell. Typing in manually the excat same formula gives me the correct answer. The formulae are exactly the same whether filled or put in manually. Is this a known glitch? Thanks Will ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ You should check, whether you activated automatic calculation in "tools"-"options"-"calculation". If it is...

Associated external account security issues?
Windows 2k AD w/Exchange 2k Trying to eliminate errors in Backup Exec after disabling accounts. Veritas whitepaper http://support.veritas.com/docs/274775 tells me to give Self account on mailbox Associated external account perms. However, they also suggest that this could be a security issue. I'd like to know why? Our Exchange server is in a well fortified domain. Do I need to be concerned about security issues? Thanks. Peter On Fri, 26 Aug 2005 07:00:03 -0700, "Peter" <Peter@discussions.microsoft.com> wrote: >Windows 2k AD w/Exchange 2k > >Trying to elim...

Outlook URL links
How do I create standard URL links in the body of an outlook message???? For example: <A HREF="http://www.bbc.co.uk">text to display</A> ...

Report Issue
I have a report that prints all of the Employees Names for a group on a preprinted paper form. I am trying to duplicate the paper form in an Access 2003 report. All works great with a subform report with a block set up in columns and there are an even number of records. When there is an odd number of records I need either a blank record input or a few lines drawn on the subreport. HOW CAN I MAKE THIS HAPPEN based on the number of records in the query. -- Hansford D. Cornett ...

Time Stamp Issue
I have it set up in my table that everytime a record is created it is time stamped with the date. What I am trying to do is set up a separate time stamp when the record is modified while also keeping the original record creation time stamp. Is this possible and if so how can I do it? I'm sorry to just jump into your post Antavas but I have a similar question and hope you don't mind. How would you do this if you want to also track changes to a subform? I have a form with a subform. The form has a date modified field. I would like to track any changes that occur, whethe...

Display name change
My problem is this: I need email for just a few users to show up as shown below. This should be seen in the "From:" field on the recipient's email client. John Doe (Contractor) <jdoe@widgets.com> Currently it shows up in the recipient mailbox as: John Doe <jdoe@widgets.com> I tried changing the display name but it doesn't seem to work any differently. Is this possible? Seems like it should be relatively easy, but I guess I'm missing something somewhere. Dave display name is in fact the attribute you need to change. Once you change it, it should show u...

Pb with Lotus Notes Link
Hi all We have Lotus Notes for some old applications, when users send link to documents in Lotus application, outlook express can open the link, but Outlook 2003 can't. In Outlook 2003 when we saw the source of mail the link is in the form notes:///C78D54..., but when you click on it, the link is transform in notes://C78D54..., one / is missing, if you made Start --> run and paste it in, add one / after notes: to have 3 / it works, do you have an idea of how we can configure Outlook 2003 to open correctly the link ? Thanks JP ...

to Make Labels with different photo and name
I probably will want the size of an 8 label page but they will have a different photo and name on each. Is this possible in Publisher 2002 SP3? Word 2002? Works 2000? I've been all over the net looking for some info to no avail. aaarrrrgh. I did make one label in Publisher and I like the looks of it but for the life of me I cannot find out how to get different ones on one sheet to print. Why can't it be as easy as dragging them? I'll make them individually instead of a mail merge. lol Thanks for any input. AnnE AnnE wrote: > I did make one label i...

how do i arrange list of names alphabetically in excel?
see i am arranging a data containing around 100 names of companies.. I dont have them in alphabetical order. i want them in that order. so i just want to know is there any function by which the list of companies would get arrange in an alphabetical order? Try from the Menu bar Data >> Sort >> Ascending -- Hope this help Pls click the Yes button below if this post provide the answer you asked cheers, francis "ganna pritesh" <ganna pritesh@discussions.microsoft.com> wrote in message news:1C5E36A3-FF29-4CC3-9E8A-7F993C70513E@microsoft.com... > see i am a...

Creating a range out of a scattered cells with MFC Excel automation
Hello all. I need to create a chart from 2 columns, but not from all of the cells in them. For example: If I need to create a line chart out of the date in colums A and D, then I know that I should use Union to create a joint range of A and D and pass it to to ChartWizard function. But what I realy need is to create the chart out of A and D columns data only if the data in column C for example, is "valid data" . How can I do this? Thanks! Probably by buying a third-party plotting library. Excel is notorious for how bad its charting mechanisms are, parti...

Links dont open
Why is it that n some emails where there are links outlook opens the explorer with strange URL like this outbind://16-0000000026322AE5531BF6438B074869664FD701440D2300/http/www.something.com ...

Can I Use AutoNumber Field In Multi-Table Link?
Is there any problem using the AutoNumber field as the primary key, and then using this field to define a 1-M relationship between two tables? I recall being warned against this. At the time, I was using another db app (Paradox). Would this warning also apply to Access? David Portwood wrote: > Is there any problem using the AutoNumber field as the primary key, > and then using this field to define a 1-M relationship between two > tables? > I recall being warned against this. At the time, I was using another > db app (Paradox). Would this warning also apply to Access? No....