Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
38
Medium Priority
?
2,249 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
Comment
Question by:Stephen Kairys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 16
38 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 12416499
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
ID: 12423564
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 81

Expert Comment

by:byundt
ID: 12425117
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 4

Author Comment

by:Stephen Kairys
ID: 12425861
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 81

Expert Comment

by:byundt
ID: 12426320
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
ID: 12426691
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 81

Expert Comment

by:byundt
ID: 12427300
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
ID: 12427370
Microft Excel 2000, (9.0.3821 SR-1)

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

Expert Comment

by:byundt
ID: 12427581
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
ID: 12427608
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
ID: 12435744
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
ID: 12449729
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 81

Expert Comment

by:byundt
ID: 12449740
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
ID: 12449755
Thanks! Have a good weekend, I'll be checking back on Tuesdayl so again, no big rush on this one.

stevefromc
0
 
LVL 81

Accepted Solution

by:
byundt earned 800 total points
ID: 12460763
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 81

Assisted Solution

by:byundt
byundt earned 800 total points
ID: 12460835
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
ID: 12473974
>>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
ID: 12474139
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 81

Assisted Solution

by:byundt
byundt earned 800 total points
ID: 12475462
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
ID: 12475546
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
ID: 12476319
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 81

Assisted Solution

by:byundt
byundt earned 800 total points
ID: 12478478
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
ID: 12478719
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 81

Expert Comment

by:byundt
ID: 12478884
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
ID: 12478925
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 81

Expert Comment

by:byundt
ID: 12478944
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
ID: 12479004
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
ID: 12485408
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 81

Expert Comment

by:byundt
ID: 12485792
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
ID: 12488034
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 81

Expert Comment

by:byundt
ID: 12489279
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
ID: 12489681
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
ID: 12562027
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 81

Expert Comment

by:byundt
ID: 12562171
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
ID: 12562265
Bingo!

Thanks again.
0
 
LVL 4

Author Comment

by:Stephen Kairys
ID: 12671073
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 81

Expert Comment

by:byundt
ID: 12671258
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
ID: 12695952
Brad,
Yeah, if I recall correctly, the Insert...Hyperlink function WAS
greyed out.  Thanks for the clarification.
Steve
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

636 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