Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinks not relative? (EXCEL 97/2000)

    Created sheet with several categories. On each category cell, I made a hyperlink which would bring the user to another sheet (in the same WB) where a short help was displayed about that category. Worked like a breeze...

    Now, I added a new category somewhere at the beginning of the list. So... I also inserted a new row in my help sheet.... Guess what? All the hyperlinks in the main sheet do NOT (not!) get updated but STILL point to the same cells as before which are therefore now WRONG!!

    Example?
    In the main sheet I have a hyperlink pointing to cell A10 in my help-sheet. If I now add a row above row 10 in my help-sheet one would expect the hyperlink to point to cell A11... [OK.... I would have expected that]... however the hyperlink STILL points to cell A10

    This seems to be "by design", (why?); any idea what I can do about that and have my hyperlinks automatically updated?????

    Thanks,

    Erik Jan

  2. #2
    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 not relative? (EXCEL 97/2000)

    They work this way for the same reason that we use range names (not literal cells) in macros. You are saving a link (that is essentially text) to "goto" cell A10 of a particular sheet. Even if you DELETE row 10 or column A insert rows, anything, You have STILL told excel to go to cell A10.

    If you wrote a macro to do this (eg)
    worksheets("sheet1").range("A10").select

    It works fine. If you move A10 in any way, it won't matter, the macro will still select A10 (it is only TEXT). If you change the sheet name the macro will give an error (as well as the hyperlink!)

    You can use the same method to get around this problem in the macr as well as the hyperlink: Use a defined name (Insert - name -define) to give a name to sheet1!A10. and use the range name as the hyperlink reference (just as you would in the macro reference). If you insert rows the range name will be updated and since the hyperlink uses the rangename it will automatically be updated.

    Steve

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

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Another way to have them automaigically updated is by using the Hyperlink worksheet function:

    =HYPERLINK("#" & CELL("Address",Sheet2!A1),"Go there!")

    This will show "Go There!" in the cell and once clicked it will take you to Sheet2, cell A1. This will update on inserting, deleting, moving, etcetera.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Thanks,

    Sounds logical and I think -subconsiously- I knew that. I already tried using names yesterday and found out that worked but didn't want to create 60+ names just for that...

    EJ

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Oh YES... beautiful... that's what I was hoping for...

    Thanks (again!!) Jan Karel

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

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Creating hundreds of names is a breeze with my name manager:

    - Create just one name
    - Use the List option from the name manager.
    - copy the line of the name you just created as many times as needed and edit the names and refersto of the copies (you can use Excel's fill handle, search and replace, etctera)
    - Select all new lines, open the name manager and click the Pickup button...

    Download at the Excel MVP page below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Hi Jan Karel

    Can you explain something please? What is the significance of the "#"?

    Regards
    Peter

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Still have to try this out but assume there won't be any major problems.... Here's a little bonus question <img src=/S/devil.gif border=0 alt=devil width=15 height=15> ... (thought this couldn't be done but now I've seen THIS can work, I thought I'd give it a go...)

    Continuing on the "hyperlink jump to help on another sheet thing"... I actually have TWO similar sheets which both have jumps to the same help location. It would be great if, arrived at that help location, I would know where I came from (so I could allow the user to jump back to the most recent sheet). Obviously with VBA this is a breeze... I was trying to think of a way without VBA.

    Using Jan-Karel's suggestion below I could make this work provided I could 'store' the sheet where the jump came from (which is also the last active sheet) somehow...

    Any ideas?

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

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Well, if you add a hyperlink to a cell on another sheet and then study the text in the hyperlink dialog, one can see Excel has prefixed the sheetname and cell address with a hash. So I figured I would have to use that in the HYPERLINK function as well.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    I would know no way to achieve this without VBA.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    As Jan Karel suggest there probably isn't a neat way to do this without VBA. That said, might the following 'kludge' work?

    On the basis that you know where you are going to, because you have physically hard coded the destination cell address in the hyperlink, you must by defination know where you came from? Ergo, could you modify Jan Karel's original idea:

    =HYPERLINK("#" & CELL("Address",Sheet2!A1),"Go there!")

    So that in, say, cell B1 on Sheet 2 (i.e. the cell next to cell you are jumping to) have something like the following:

    =HYPERLINK("#" & CELL("Address",Sheet1!A1),"Return")

    Which returns you from where you just came. Change the tags to something relevent and it may work? It could probably all be made a bit neater with some form of lookup table, assuming the basic principle suited your purpose?

    Regards
    Peter

  12. #12
    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 not relative? (EXCEL 97/2000)

    The problem is the desire to use the hyperlink not as a GOTO (as it does) but more like a GOSUB. When you hit a "return" you go back to where you STARTED.

    The question posed was if you go to the SAME point from a hyperlink, how can you return without VB. Your method assumes you can from Sheet1!A1, but what if you didn't but went to Sheet2!A1 from Sheet3!A1 instead. Your "return" would sent them to Sheet1 which is NOT where you started from.

    I agree with Jan, it is NOT possible without code. Not Even the "back" button on the browser toolbar does it.

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    OK.... I have stood at the back of the class for a few hours and wore the dunces hat.... While there I came up with plan B which is attached , however I am stuck on something.. Perhaps you and/or others could bring your considerable talents to bear on finding a solution? I need to find a way to force a re-calculation event whenever a hyperlink is followed to the help sheet. Any way that can be achieved??

    The attached solution is still a kludge and I agree a much tidier solution could only be achieved via VBA.

    Regards
    Peter

  14. #14
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinks not relative? (EXCEL 97/2000)

    Re the "#" thing - Thanks Jan Karel, I understand now. I tried deleting the "#", and you get a "File Not Found" error, so I guess the "#" mus be shorthand to the Excel HYPERLINK command to use the current file.

    Regards
    Peter

  15. #15
    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 not relative? (EXCEL 97/2000)

    If you are going to force a recalc event you are going to probably use VB.
    If you are going to use VB the question of doing some of these things without VB is moot.
    Instead of hyperlinks, why not add a hidden label to each of the cells, with a macro which goes to a location and (via the macro) adds the location for the return macro and in the process recalc the sheet? You then accomplish the goto a cell and then return to where you started.

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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