Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Delete names - update references (2000)

    Hi!
    We've got an excel workbook with a lot of named cells. Now the "customer" wants to get rid of most of the names. Deleting them will off course cause #NAME-errors to occur in the cells using the name as reference.
    Is there an easy way to update formulas using names so they are using ordinary reference instead? Doing this manually will take some time :-)
    I've installed the NameManager extension, but it seems not to do the trick - or am I missing out on something?

    Roy

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete names - update references (2000)

    Hi Solomon,

    I found this macro which "claims" to do what you need. I would recommend to try it on a copy of your data before you do it on the real sheets!
    Replace Names with References Macro
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete names - update references (2000)

    Good catch Rudi.

    Note that it will only work with named ranges, not with named formulas
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete names - update references (2000)

    It is not a part of the Name Manager, that is correct.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete names - update references (2000)

    Thanks for pointing that out. I did not note that...and I have not tested the code out either!
    Cheers
    Regards,
    Rudi

  6. #6
    New Lounger
    Join Date
    May 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete names - update references (2000)

    Thanks. Seems to do most of what I want. The problem is that I want to keep some of the names. I probably just stick to Search-replace :-) Thansk anyway for your suggestion

    Roy

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Delete names - update references (2000)

    If it works well and does the job, and you only want to keep certain names...then consider running the macro to change the names to references, and then change the references of the couple you want to keep manually again.
    (Its a bit of reverse phycology - <img src=/S/wink.gif border=0 alt=wink width=15 height=15>)
    Regards,
    Rudi

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete names - update references (2000)

    You might consider using my Flexfind utility for that, as it is capable of S&R inside many Excel objects that might contain references to names as well:

    <table border=1><td valign=top>Object / Search type</td><td valign=top>Formulas option shows:</td><td valign=top>Values option shows:</td><td valign=top>Cells</td><td valign=top>Formula</td><td valign=top>Value</td><td valign=top>Names</td><td valign=top>Formula</td><td valign=top>Formula</td><td valign=top>DrawingObjects</td><td valign=top>OnAction (attached macro)</td><td valign=top>Caption</td><td valign=top>Form or Toolbox Controls</td><td valign=top>ListFillRange and LinkedCell</td><td align=right valign=top></td><td valign=top>ChartObjects</td><td valign=top>Formula</td><td valign=top>Caption</td><td valign=top>Comments</td><td valign=top>Text</td><td valign=top>Text</td><td valign=top>FormatConditions</td><td valign=top>Formula1 and Formula2</td><td valign=top>Formula1 and Formula2</td><td valign=top>Validations</td><td valign=top>Formula1 and Formula2</td><td valign=top>Formula1 and Formula2</td><td valign=top>Hyperlinks</td><td valign=top>Address</td><td valign=top>TextToDisplay</td><td valign=top>PivotTables</td><td valign=top>SourceData</td><td valign=top>SourceData</td><td valign=top>Querytables</td><td valign=top>CommandText and Connection</td><td valign=top>CommandText and Connection</td><td valign=top>Headers and footers</td><td valign=top>Their text</td><td valign=top>Their text</td><td valign=top>ChartObjects are scanned for titles, axis titles, series formulas, embedded objects, etcetera.</td><td align=right valign=top></td><td align=right valign=top></td></table>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •