PDA

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...

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...

09-03-2005, 03:50 PM
In order to make this function work ...

In Sheet1!A1 enter this formula...
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...