Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    copying names from one sheet to another (XL97 sr2 on Win2000/NT4)

    I have a multi page spreadsheet that includes several dynamic named ranges. I also have <!post=Name Manager,206641>Name Manager<!/post>.

    Name Manager lets me turn a global name into a local name but what I want to do is make localised copies from my global names.

    (I'm) stuck

  2. #2
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying names from one sheet to another (XL97 sr2 on Win2000/NT4)

    Further to my original post I have come up with a work around involving:
    -opening a second instance of the spreadsheet
    -localising the global names on to page 1
    -copying the complex dynamic ranges
    -pasting into new ranges on the first instance of the sheet to create new global ranges
    -localising these new ranges onto page n
    -finally I can re-globalise the local names on page 1

    Fun it's not, elegant it most certainly is not but it works. The task is marginally eased by the fact I have a dual monitor set up so can see both instances of Excel at the same time.

    (wading through mud rather than) stuck

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

    Re: copying names from one sheet to another (XL97 sr2 on Win2000/NT4)

    So let name manager create a List, which will look like this:

    <table border=1><td valign=bottom>TotalEmps</td><td valign=bottom>=A3_UniVerse!$F$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalMobPhones</td><td valign=bottom>=A3_UniVerse!$D$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalMobSpend</td><td valign=bottom>=A3_UniVerse!$E$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalOurPhones</td><td valign=bottom>=A3_UniVerse!$G$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalOurSpend</td><td valign=bottom>=A3_UniVerse!$H$12</td><td align=right valign=bottom>True</td></table>

    Then copy the names you want localised and edit them (if needed also edit their refersto):

    <table border=1><td valign=bottom>TotalEmps</td><td valign=bottom>=A3_UniVerse!$F$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalMobPhones</td><td valign=bottom>=A3_UniVerse!$D$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalMobSpend</td><td valign=bottom>=A3_UniVerse!$E$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalOurPhones</td><td valign=bottom>=A3_UniVerse!$G$12</td><td align=right valign=bottom>True</td><td valign=bottom>TotalOurSpend</td><td valign=bottom>=A3_UniVerse!$H$12</td><td align=right valign=bottom>True</td><td valign=bottom>Sheet1!TotalEmps</td><td valign=bottom>=A3_UniVerse!$F$12</td><td align=right valign=bottom>True</td><td valign=bottom>Sheet1!TotalMobPhones</td><td valign=bottom>=A3_UniVerse!$D$12</td><td align=right valign=bottom>True</td><td valign=bottom>Sheet1!TotalMobSpend</td><td valign=bottom>=A3_UniVerse!$E$12</td><td align=right valign=bottom>True</td><td valign=bottom>Sheet1!TotalOurPhones</td><td valign=bottom>=A3_UniVerse!$G$12</td><td align=right valign=bottom>True</td><td valign=bottom>Sheet1!TotalOurSpend</td><td valign=bottom>=A3_UniVerse!$H$12</td><td align=right valign=bottom>True</td></table>

    Now select the NEW rows, start the name manager and click "Pickup". Done.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copying names from one sheet to another (XL97 sr2 on Win2000/NT4)

    AHH <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    I can follow the theory but my few attempts so far have ended with a 'that name is not valid' error <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>. Even so, as this is clearly the proper way to solve my problem I will persevere.

    My sheets include an underscore in their names but that didn't cause a problem when I did it my way.

    stuck

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

    Re: copying names from one sheet to another (XL97 sr2 on Win2000/NT4)

    You might have to add single quotes around the sheetname:

    'TheSheet_Name'!TheLocalName
    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
  •