Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    Bayonne, New Jersey, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hyperlinks (Excel 97)

    Hi everyone,
    So I created a spread sheet with multiple sheets. Sheet one contains information with hyperlinks to other sheets for more detailed information. I want to use this report as a template to create another report but need to change the names of the sheets. When I rename the sheets, it breaks the hyperlinks that were created in sheet one.

    Can you help?

    Elaine

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlinks (Excel 97)

    You will get this behaviour "by default". Is there any way that you can structure your reports so that you can keep the same Sheet names? HTH
    Gre

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: hyperlinks (Excel 97)

    A hyperlink is essentially "Text" in a region.

    The "easiest solution" is using the same principle that works in macros. instead of hyperlinking to explicit cells in the sheets, NAME the cells you want to go to first (insert - name - define), then in the named location in file put the NAMED cell. This way if you change the worksheet name, EXCEL will update sheet name in the named cell and the hyperlink will still work.

    Another option is using the HYPERLINK function combined with the ADDRESS function:

    <pre>=HYPERLINK((ADDRESS(1,5,,,A1)))</pre>


    will link to cell E1 with the name of the sheet in cell A1. For this to work every link to a sheet should be to a particular cell.

    Then in A1 you could enter something like:
    <pre>=MID(CELL("filename",Sheet1!A1),FIND("[",CELL("filename",Sheet1!A1)),100)</pre>


    This gets the workbook and sheet name "extracted" from the path. When/if you change the sheetname the CELL function will automatically be updated and thus the HYPERLINK will be updated also.

    The only other way is a macro to check through the hyperlink objects and change them from a particular name to the new name. This could be automated (i suppose) by creating a "table" of before and after names. The macro could search through the table looking for the before name and replacing it with the after name. This would NOT be live and the table would have to updated whenever sheet were renamed.

    Steve

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    Bayonne, New Jersey, USA
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hyperlinks (Excel 97)

    Sorry it took so long for me to thank you. This was easy and worked out extremely well. Thank you soooo much.

Posting Permissions

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