Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loosing my links (Office 97)

    Can anyone tell me what I'm doing wrong or perhaps I should have an additional setting somewhere or perhaps an add-in. Hopefully I can explain what I have correctly.

    I have a workbook that has about 50 tabbed spreadsheets in it. The form for all the sheets but the first are identical until it grows with information. The first sheet is a summary page with information linked and hyperlinked from particular cells from all the rest.

    Since I have so many individual sheets, I hyperlinked the "name" of the individual sheets which is in cell A1 to it's corresponding location on the summary page and back again. In other words, if I click on the name in the summary page it will go to that worksheet and then if I want to go back to the summary page from the individual worksheet I click on the name (A1) and it goes back again. I can go back and forth easily. Works really well.

    My problem is when I insert a new row on the summary page to include a new spreadsheet that I just created, all the links do not adjust themselves to the new location of the cells from that point down. I have the checkbox checked for "Use relative path for hyperlink" in the Edit Hyperlink window. I tried un-checking it, but that doesn't make any difference.

    Any ideas? Thanks!
    Louise

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Use defined names rather than cell references

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    What do you mean by "defined names"?
    Louise

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Click on a cell, use Insert/Name/Define, make up some name to represent the cell, like xyz (use a more meaningful name). Then, instead of using sheet2!$D$6, for example, you use =xyz. When you insert/delete rows, the reference to the defined name won't be lost as you're finding with the hyperlinks to cell addresses.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Bob, Thank you very much!
    Louise

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Hi Bob,
    I tried it, but kept getting the error message that the "name is not valid" until I tried just using letters. Does it always have to be letters? Nothing numeric?
    Again, Thanks for all your help.
    Louise

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Names can consist of letters and numbers and "_". No special characters, no periods, no BEGINNING with numbers, no spaces.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    And nothing that looks like a cell address, like A1.
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Oops. Right. But also R1C1 etc. is not a good idea.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Thanks guys, your help is much appreciated.

    Now that I've "renamed" some cells, can I still use the copy and "Paste LInk" or "Paste as Hyperlink" feature? or will it still reference a cell address. Right now I'm working with a spreadsheet that I've used for years and the links are there already (some mis-aligned). Is it too late to "name" the cells or should I start over by deleting all the links, name the cells and then re-link / re-hyperlink them.
    Louise

  11. #11
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    It's never too late to name the cells. To see if you can still use the paste link/paste as hyperlink feature, best way to learn it is to try it! Naming the cells won't change your formulas to the names, but you can use Insert/Name/Apply, select all the names, and click OK -- this will change $D$5 to "WhateverName", for example, and can speed up the process.

  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Florida, USA
    Posts
    394
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loosing my links (Office 97)

    Bob, Thanks so much! What a neat thing to know! That'll save a lot of work!
    Louise

Posting Permissions

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