Solved

Hyperlink to another spreadsheet in workbook does not work if I add rows to the target spreadsheet

Posted on 2004-10-26
2,203 Views
Last Modified: 2007-12-19
Suppose I have an EXCEL workbook with two spreadsheets. Call the sheets
SUMMARY and DETAIL.

In SUMMARY, cell A3, I have a hyperlink to cell A6 in DETAIL.  So far, so good.

Now, however, out of necessity, I need to add a few rows to DETAIL, so A6 becomes A8. Now, my hyperlink does not work for me, since A6 now has different data.

Is there a way to protect my hyperlink against adding  cells to the target spreadsheet (or to hyperlink in a different manner?)

Thanks
stevefomc
0
Question by:Stephen Kairys
    38 Comments
     
    LVL 80

    Expert Comment

    by:byundt
    Hi stevefromc,
    Try using the HYPERLINK function with the CELL function. For example:
    =HYPERLINK(CELL("address",DETAIL!A6),"Detail for this item")
    When you insert one or more rows, the reference in the CELL function automatically changes.

    The first parameter in HYPERLINK is a string containing the address of the link (provided by the CELL function). The second parameter is a "friendly name" that appears in the cell containing the HYPERLINK formula.
    Cheers!

    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,
    Thanks for the reply.

    I found the hyperlink function (by going to INSERT, then FUNCTION, then choosing HYPERLINK
    on the right). Doing so brings up a box with two fields:

    LINK LOCATION
    FRIENDLY NAME

    Now, for LINK LOCATION, I understand the DETAIL!A6 part (cell A6 in spreadsheet DETAIL),
    but WHAT is address, and do I need to put it in quotes if I'm using the dialog box?

    For FRIENDLY NAME, that's the text that appears in the cell I'm linking FROM, right? And,
    do I need to put it in quotes if I'm using the dialog box?

    Thanks  again,
    stevefromc
    0
     
    LVL 80

    Expert Comment

    by:byundt
    stevefromc,
    You need to use the CELL function inside the HYPERLINK function to get it to move with row insertions. HYPERLINK is expecting text string arguments--not cell references. The CELL function provides that string argument (the address for cell A6 on the DETAIL worksheet). "Address" is a text literal being used as a parameter in the CELL function--you must enter it exactly like that. The cell reference (second parameter) makes the CELL function return a dynamic string argument to HYPERLINK--one that will change when you insert rows.

    The "Friendly name" is what you see in the cell. It can be any text string you like. Because it is a string, it needs to go inside double quotes. Excel 2003 is smart enough to add the double quotes if you use the Fx icon (or Insert...Function menu item) to insert the function.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Well, I tried it, but still having difficulty as follows. Suppose the cell on the original worksheet
    is A2:


    ***CAUTION- TO ANYONE READING THIS, THESE STEPS ARE NOT A SOLUTION, BUT
    A DESCRIPTION OF SOMETHING THAT MAY NOT HAVE WORKED!! ***

    1. Go to Cell A2
    2. INSERT -> FUNCTION
    3. Click on the red arrow to the right of the LINK_LOCATION field.
    4. The cursor now becomes a + sign.
    5. Click on the tab for the DETAIL worksheet
    6. Click on Cell A23. I see a moving borrder around it.
    7. On top of the screen are some tabs/fields regarding the hyperlink. I then clicked on the
         red arrow to the right of the field that now says "Detail"|A6
    8. The hyperlink dialog box re-appeared.
    9. I go to the FRIENDLY_NAME field and typed (WITH the quotes, as I have Excel2000),
        "This is my link"
    10. Clicked OK. For some reason, I get a gray box telling me that the macros are disable (why?).
          I click OK on that box as well.
    11. I now see my hyperlink. When I click on it, however, I get the error "Cannot open specified
    file".

    What did I miss?
    Thanks
    stevefromc

    0
     
    LVL 80

    Expert Comment

    by:byundt
    stevefromc,
    Please paste one of the following worksheet formula in cell A2:
    =HYPERLINK(CELL("address",DETAIL!A23),"This is my link")
    =HYPERLINK("[Book2.xls]" & CELL("address",'Detail sheet'!A6),"This is my link")

    Now, you can click on cell A2 (which will say "This is my link"), and you are transported to the DETAIL worksheet, cell A23. The second form of the formula is required if the worksheet name includes a space--note the single quotes surrounding the worksheet name.
    *****If you don't use the CELL function to return the address of DETAIL!A23, the link won't adjust when you insert or delete rows*****


    The problem with the method you tried in your last comment is that you didn't enter a complete address as a text string for the first parameter of the HYPERLINK function. Your result should have looked like one of the following:
    =HYPERLINK("[Book2.xls]'Detail'!A6","This is my link")                    single quotes surrounding Detail are optional
    =HYPERLINK("[Book2.xls]'Detail sheet'!A6","This is my link")            single quotes surrounding Detail sheet are required
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    OK, we're getting there. After I tailored the second option you gave:

    =HYPERLINK("[Book2.xls]" & CELL("address",'Detail sheet'!A6),"This is my link")

    to the ACTUAL names of my workbooks, worksheets, etc. cell A2 on the original worksheet now hyperlinks to A6 on the detail sheet; however, it does not LOOK like a hyperlink (is not blue/underlined).

    Also, was there a reason in your first example that you omitted the BOOK2.XLS?

    Thanks.


    0
     
    LVL 80

    Expert Comment

    by:byundt
    stevefromc,
    I didn't post the more complicated formula because it wasn't required in my test workbook. When you had problems implementing the formula, I added the complication in case you were using a different worksheet name than shown in the original question--and in so doing learned that the workbook name would be necessary when the single quotes were added around the worksheet name. And you need those single quotes if the worksheet name has an embedded space.

    In my Excel 2003 test workbook, the "This is my link" is blue and underlined before I click on the link. It is magenta and underlined after the link has been clicked. Formatting of the cell is controlled by the Format...Cells menu item--you will see that single underline and blue font color are both selected after you put a HYPERLINK formula in a cell.

    Which version of Excel are you using? I'd like to test in the same version.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Microft Excel 2000, (9.0.3821 SR-1)

    Thank you for your continued efforts and interest in this problem!
    stevefromc
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Steve,
    The HYPERLINK formula works the same on my Excel 2000 (service release 3) as in Excel 2003. The link moves when you insert a row on the destination sheet. And the departure cell uses a blue font with single underline before clicking on the link; magenta font with single underline afterwards.

    This behavior is specified by the "Hyperlink" and "Followed hyperlink" cell styles. You can see how your install expects to handle hyperlinks by selecting a cell with a link, then opening the Format...Style menu item. Choose either "Hyperlink" or "Followed hyperlink" styles in the "Style name" field. You can change the appearance using the "Modify" button.

    The Microsoft KnowledgeBase lists this article discussing unexpected colors with hyperlinks: http://support.microsoft.com/default.aspx?scid=kb;en-us;202355

    I didn't see anything suggesting a bug with hyperlinks in Excel 2000 SR-1. Even so, you may still want to update your Office to the latest service release.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,
    I'll check into the above tomorrow; about to sign off for the day. Thanks again, and I'll advise
    you tomorrow of further issues.

    stevefromc
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,

    Well, I tried going through your steps. I opened the FORMAT->STYLE menu, and selected
    FOLLOWED HYPERLINK.  It was underline, color 29. I then went into MODFIY, chose
    the FONT tab, and saw in the PREVIEW field that the FOLLOWED HYPERLINK was purple-underline which seems OK. I then clicked on OK (I think w/o changing anything) and poof - the link
    now shows in purple underline.  Then, just to cover myself, I did the same thing for HYPERLINK
    (which is BLUE underline).

    I'm not sure why going through these steps  (essentially changing nothing)  would result in this behavior.

    In any event, getting back to the original question, for future links I need to create, will I have
    to type in the link each time (or paste it in), rather than use the INSERT->FUNCTION method?

    Thanks.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Hi, Brad. Did you get a chance to review my response
    above?  Thanks....no rush on responding right away, I'm
    getting ready to log off until Tues AM but if you get a chance
    to review it Monday I'd appreciate it.

    Thanks again.
    stevefromc
    0
     
    LVL 80

    Expert Comment

    by:byundt
    stevefromc,
    I thought a more appropriate response would be to write a macro that lets you click on a cell, then builds the HYPERLINK formula for you. That's a project for later tonight.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Thanks! Have a good weekend, I'll be checking back on Tuesdayl so again, no big rush on this one.

    stevefromc
    0
     
    LVL 80

    Accepted Solution

    by:
    stevefromc,
    Hyperlinks are normally static—they do not readjust themselves if the target is moved (such as by inserting or deleting rows or columns). The InputHyperlink macro builds a dynamic HYPERLINK function formula in the active cell that automatically adjusts when the linked cell is moved. The macro first asks you to point to a cell. Next, it asks you to enter a "friendly" name for the link. The linked cell may be in another workbook. If the workbook is closed when the link is clicked, the workbook will be opened. The formula it builds looks like this:
    =HYPERLINK("[Insert hyperlink.xls]" & CELL("address",Sheet2!$A$1),"Friendly name for my link")

    Paste the macro in a regular module sheet.

    Sub InsertHyperlink()
    'Puts a dynamic hyperlink formula in the active cell. Formula automatically adjusts if its target is moved.
    Dim cel As Range
    Dim frmla As String, friendly As String

        'Select a target cell in this workbook, or any other open workbook
    On Error Resume Next
    Set cel = Application.InputBox("Please select the target cell you want to link to", _
        Title:="Hyperlink Function Builder", Type:=8)
    If Err <> 0 Then Exit Sub   'If user hits 'Cancel', then an error occurs--and sub is exited
    On Error GoTo 0

        'Build strings for the "friendly name" displayed in the link, and for the dynamic HYPERLINK formula
    friendly = InputBox("Please enter the text you want displayed in the cell containing the link", _
        Title:="Hyperlink Function Builder", Default:=cel.Address(external:=True))
    frmla = "=HYPERLINK(""[" & cel.Parent.Parent.Name & "]"" & " & _
        "CELL(""address"",'" & cel.Parent.Name & "'!" & cel.Address & "),""" & friendly & """)"

    ActiveCell.Formula = frmla  'Put the HYPERLINK formula in the active cell
    End Sub

    To install a sub or function, ALT + F11 to open the VBA Editor. Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

    To run a sub, ALT + F8 to open the macro window. Select the macro, then click the Run button.

    If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

    Brad
    0
     
    LVL 80

    Assisted Solution

    by:byundt
    stevefromc,
    When you use the Fx icon (or Insert...Function menu item) to open the Function Wizard, you need to be careful what type of values the function is expecting. HYPERLINK function needs text strings for both of its two inputs. If you point to a cell (by clicking on the Browse button at the right), you are putting a cell pointer into the function rather than the expected text string. As a result, the formula will fail.

    FWIW, I have found at least a dozen wrong ways to solve this problem. The suggested formula (and macro that builds it) in my last post have been tested on links to the same worksheet, links to a different worksheet, links to a different workbook, links to a closed workbook (which was open when the link was built), and links to a block of cells. I found some formulas that didn't work at all--and others that worked in some cases, but not all. Suffice to say, most problems take me a lot less time to solve than the three plus hours I've invested in this one.

    Please let me know how you like the macro. As long as a workbook containing the macro is open, you can use it in any other open workbook.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    >>When you use the Fx icon (or Insert...Function menu item) to open the Function Wizard, you need to be careful what type of values the function is expecting. HYPERLINK function needs text strings for both of its two inputs. If you point to a cell (by clicking on the Browse button at the right), you are putting a cell pointer into the function rather than the expected text string. As a result, the formula will fail.<<

    Brad,
    Please clarify. You're saying that using a cell pointer is
    incompatible with the expected text strings causing the formula
    to fail, so how do I work around it?

    Thanks for your efforts on this matter. I will try it sometime
    today.

    stevefromc
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,

    OK, I have the macro up and running. However,
    when it asked for my reference I got stuck. I typed in the name
    of the "target" spreadsheet and I got a gray error window that told me:

    "The reference you typed was not valid, or you did not provide a reference where one was required

    To enter a reference using the mouse, click in the edit box, then click the cell in the sheet you want to refer to, or drag through the range you want to select."

    What edit box?? I tried clicking on EDIT on the toolbar but to
    no avail.

    Please advise.


    Thanks
    0
     
    LVL 80

    Assisted Solution

    by:byundt
    For the the first input box, you point with your mouse to the target cell (the one you want to link to). Change sheets or workbooks as necessary. The macro will take care of filling out the input box text. Then click OK.

    For the second input box, you type in a "friendly" name then click OK. This friendly name will be displayed in the cell containing the HYPERLINK formula.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Wow!!!

    I tried it once, and it worked.  Only problem is that my "friendly" name was too long to fit in the cell, and it overflowed onto the second cell...there's no way to pick the FONT for the friendly name, is there? (I was thinking of using a smaller font).

    I will play around with it some more over the next day or so. If this continues to work,
    I will be giving you some additional points for your extra effort.

    Thank you.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    One more thing.......after hitting ALT-F11 to return to the spreadsheet, at what point do you
    recommend closing out the VB window altogether.

    PS-I just tried it for a real-life issue I had to add to my spreadsheet and it worked!
    0
     
    LVL 80

    Assisted Solution

    by:byundt
    stevefromc,
    You may close the VBA Editor window any time you like. It does not need to be open for the macros to run.

    Font size and type are controlled just like any other cell--use the Format...Cells...Font menu item. The font color and underline are controlled by the Format...Style menu item, as previously discussed.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Well...this is strange. I went into Format...style to try to fix the font color & underline. At the time, I was in a cell that does NOT contain my hyperlink.  Somehow, that cell now has its contents
    in blue-underline. Moreover:
    * Even if go into format-cell and turn off the blue color, it stays blue, and the formatting does NOT show that it's underlined.
    * Style is grayed-out on the format menu.

    Any ideas here?
    Thanks
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Did you change the "Normal" style? You should have selected either the Hyperlink or Followed Hyperlink style to modify.

    The active cell when you modified the Hyperlink or Followed Hyperlink style will inherit that style. You should select that cell (or a range of cells including that one), then open the Format...Style menu item and select the Normal style.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    OK. I got that cell back to normal,then went to the hyperlink cell, and changed the font to 8 and all is well there :)

    Now..can I assume that EXCEL  will REMEMBER this change and apply it (i.e. make the font 8cpi) to all future hyperlinks I create?

    Thanks again. I think we're just about there!!
    0
     
    LVL 80

    Expert Comment

    by:byundt
    I believe the Style will be changed just for that workbook. But Excel should remember it for that workbook each time you open it.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Cool.

    I will review everything tomorrow and if no problems occur,
    close out the question.

    Thx again.
    Steve
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,

    Issue closed. I have increased the points in appreciation of your efforts "above and beyond
    the call of duty". I will be "splitting" the points between several of your answers as you
    provided good info in more than one of your responses regarding the macro.

    THANKS AGAIN!!!
    Steve
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Steve,
    Thanks for the point increase and grade! This has been a fun problem to work on, so I hope to see you in a future question.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Hey, Brad.

    I hope you get this msg (as the question is already closed) but Ijust hit a minor issue.
    I just added a row to the spreadsheet below a row containing a hyperlink (say in A12).
    Now, when I type text on A13, that cell seems to "inherit" the hyperlink attribute from the cell
    above it (A12). Can I avoid this "inheritance"?

    thx
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Steve,
    I don't think you can avoid the inheritance--it's not a bug, but rather a "feature" of Excel. The same thing would happen if you formatted a cell with %, then inserted a row immediately underneath. Excel would assume that the new cell should also be formatted as %.

    If you don't like this behavior, then copy a blank cell in another part of the spreadsheet and paste it underneath your hyperlink. That step will overwrite the assumed formatting.
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Well, I tried a variation of your idea...copied the contents of a non-hyperlink cell. That seemed
    to work, and I can certainly live with that "feature". :)

    Thanks again.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Hey, Brad,

    When using the macro tonight, I got the following error
    (as closely as I can remember):

    Run Time Error 1004,
    Application-defined or object-defined error.

    I went into the VB Debugger and the following line
    (right at the end of the macro) was highlighted:

    ActiveCell.Formula = frmla  'Put the HYPERLINK formula in the active cell

    I think I know why: my friendly name included ""s e.g.

    Test of "macros" in EXCEL.

    Could the quotes be the culprit?

    Btw, this little matter aside (which was easily solved
    by not using quotes), I'm enjoying using it!

    Thanks
    Steve
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Steve,
    Try doubling the quotes when entering your friendly name:
    Test of ""macros"" in Excel

    It will show up in the cell like this:
    Test of "macros" in Excel
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Bingo!

    Thanks again.
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Hi Brad. Happy Thanksgiving.  No rush on this answer (I'm about to log off 'til Monday) but I was a little surprise that your macro actually "ran" when I had my workbook "shared".

     I was under the impression that you could not add a hyperlink to a shared workbook.

    Thanks again.
    Steve
    0
     
    LVL 80

    Expert Comment

    by:byundt
    Steve,
    I think it was working because the macro uses a HYPERLINK function rather than inserting a hyperlink directly in the cell. The Insert...Hyperlink menu item ought to be greyed out when the workbook is shared.

    While the workbook was shared, I found that I could create and use a formula that looks like the following:
    =HYPERLINK("[Book2.xls]" & CELL("address",'Detail sheet'!A6),"This is my link")
    Brad
    0
     
    LVL 4

    Author Comment

    by:Stephen Kairys
    Brad,
    Yeah, if I recall correctly, the Insert...Hyperlink function WAS
    greyed out.  Thanks for the clarification.
    Steve
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Article by: dandraka
    There are several quick shortcuts that can make your life easier in Microsoft Programs.  These simple tips and tricks will your work more productive and you faster at completing your tasks! MS Word (1) Creating Re-usable Scraps You can create s…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
    This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

    860 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now