Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Best way to link to another place in the workbook?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the best way to link to another place in an XL97 workbook?

    I could not find any bookmark or goto function. I could do it with VBA and a button, but I prefered a simple hyperlink equation.

    My solution was:
    S4: =CELL("filename",References!$C$1)

    N1: =HYPERLINK(MID($S$4,SEARCH("[",$S$4),LEN($S$4)-SEARCH("[",$S$4)+1)&"!$A$1","REFERENCE")

    This works and will accomodate both workbook name and sheet name changes but will fail if the target cell $C$1 is deleted.

    I tried Insert > hyperlink, but that failed as soon as I saved the workbook with a new name.

    Thanks in advance

    Brian

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Brian,

    Have you tried changing from absolute to
    relative i.e $C$1 to C1?

    James

  3. #3
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What's your objective ? Just have value in one cell be reflected in the second ?
    I feel I'm not understanding the problem.

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    The objective is to have a hyperlink in a cell labelled "Help" that will jump to the "help" sheet.

    That's what the existing formula does, but i expected there to be a simpler method (like bookmarks in a word doc).

  5. #5
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    How about using the "custom view" feature on the "views" menu.
    You select the sheet where your help is. Make this a custom view and then just select this view from the "drop down" view list that will appear on the tool bar .

    [ This Message was edited by: Nimrod on 2002-05-04 22:35 ]

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Benicia, CA
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well the way that i would do it would be to use Defined names. On the "Help Sheet", select the cell (i.e. A1) that you would like the link to take you to and then go INSERT->NAME->DEFINE.
    Now just type what you would like to call the define name. I would suggest just calling it Help. Now go back to the original sheet that will have the hyperlink on it. You can either use a button or just type text "Help" and hyperlink that. When using the hyperlink, make sure you use the Defined name of Help as the hyperlink. Not sure how XL97 will prompt you for this, so i can't help you there. Now after you do that, when you click the text or the button it will bring you to that Defined Name which will be on your "Help" Sheet.
    I like this method better than just using the hyperlink as "Help!A1" because it will always bring you to that cell even if the tab name has been changed.
    Let me know if this works for you. Hopefully i completely understood your question.

    Greg

  7. #7
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Create a view
    Change the settings that you want to save in the view. Learn about settings that are saved when you create a view.
    On the View menu, click Custom Views.
    Show Me
    Click Add.
    In the Name box, type a name for the view.
    Under Include in view, select the options you want.


    Display a view
    On the View menu, click Custom Views.
    In the Views box, click the name of the view you want.


    Source is excel help ... just search for infor on "custom views"







  8. #8
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The nice thing with using the custom view is that you can be on any page of the workbook and access the help sheet by just clicking on it's view on the tool bar.
    If you use a particular cell for the helplink then you have extra steps to get to help that don't occur with the custom view.

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks to all.

    Yes, Insert Hyperlink > Named Location > Browse > Defined Name > Name does work and withstands file and sheet name changes.

    {Note that Insert Hyperlink > Named Location > Browse > Sheet Name > Sheet fails if the sheet name changes, but is ok when the filename changes.)

    (Note that simply hyperlink(Name,"Name") does NOT work.)

    Custom views are cool! Except that I need to add code to ensure that the custom toolbar is shown (yes, already attached it tho the workbook).

    Thanks again,

    Brian

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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