View Full Version : Excel Hyperlink problem

brian-zl1thf

08-03-2005, 02:48 PM

I have an excel workbook with two sheets.

Sh1 has several columns, a1 contains James, b1 contains Harriet, plus others with data not pertinent

Sh2 has the same data on the columns a & b.

I want to use a Hyperlink on James Sh1 to point to Harriet Sh2, and another on James Sh2 to point to Harriet Sh1. So far no problem & this works.

The trouble is when I add more names to either sheet then Data:Sort. This displaces the row with the "active" names & the pointers are now disconnected.

Can anyone solve my problem please to keep the pointers correct and active

thanks

Parry

08-03-2005, 09:14 PM

Hi Brian, welcome to the board. :)

Im not a huge fan of the way MS have implemented hyperlinks in Excel. The value that points to a cell is text so it will always point to a particular reference regardless. To get around your problem you can use the Match function to determine the row as it looks for a value and returns the position of the matched cell within the range. Using this inside the address function gives you the ability to find the address of the cell and this can then be joined with the hyperlink text to always point to the correct value.

Heres an example...

You have a workbook named Book1.xls and in Sheet2 you have these values in cells A1:A6 = {a,b,c,d,e,f}. You want to have a hyperlink with the text "Link to A"in cell A1 of sheet1 and the always point to the cell with a in sheet2

In Sheet1!A1 enter this formula...

=HYPERLINK("[book1.xls]Sheet2!" & ADDRESS(MATCH("A",Sheet2!A:A,FALSE),1),"Link To A")

The second argument of the Address refers to the column number, so as we are looking at column A in sheet2 this is a 1. It would be 2 for column B, 3 for column C etc.

Now sort the text in sheet2 and you will see the hyperlink goes to the correct cell.

hth

EDIT: PS: When using Match to find the row Im assuming your data begins in row 1. if your data doesnt begin until another row you will have to add a fixed value. Eg if your data starts in row 5 then the formula would be...

=HYPERLINK("[book1.xls]Sheet2!" & ADDRESS(MATCH("A",Sheet2!A:A,FALSE)+4,1),"Link To A")

rad_s4

09-03-2005, 03:50 PM

In order to make this function work ...

In Sheet1!A1 enter this formula...

=HYPERLINK("[book1.xls]Sheet2!" & ADDRESS(MATCH("A",Sheet2!A:A,FALSE),1),"Link To A")

you must remove the .xls from the formula.

Nice work parry.

Parry

09-03-2005, 08:13 PM

In order to make this function work ...

In Sheet1!A1 enter this formula...

=HYPERLINK("[book1.xls]Sheet2!" & ADDRESS(MATCH("A",Sheet2!A:A,FALSE),1),"Link To A")

you must remove the .xls from the formula.

Nice work parry.

Thanks Rad-s4 :thumbs:

You actually do need to have the xls piece. When you save a workbook the name in the title bar and under the Windows option from the Menu bar will include xls so you must include it in the formula. However, if the book hasnt been saved yet then xls will not be in the name (ie its simply Book1 rather than book1.xls). Perhaps you tried this on a book that hadnt been saved yet?

cheers

Graham

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.