Thread: Formula involving hyperlinks

Here is the situation:

I have numerous values in column A in my workbook. I have other numerous values in Column A on another tab. Most of these values can be found on both tabs, however, the information in other columns are different. What I would like to do is Find the matching Value and return either a Hyperlink to the match, or a way to get to it with ease (without using the "Find" Function.

Anyone have any idea's or tips?

Thanks

Here's a formula that links to a different sheet. It examines cell B2 on the current sheet and searches for an answer on a sheet called Detail in column A. Adjust the formula for your need and put in an empty column next to your values. Then copy down.

=HYPERLINK("#"& "Detail!" & ADDRESS(MATCH($B2, Detail!$A:$A, 0), 1), "Link") 'diff sheet

http://www.excelforum.com/excel-gene...ab-to-tab.html

Like this:
=HYPERLINK("[C:\<path>\Book2.xls]Sheet1!"&ADDRESS(MATCH(A3,[Book2.xls]Sheet1!$A:$A,0),1),"link")

(You don't need the full path for the match if the other book is open, but you do need it for the hyperlink)

hth

1. I didn't read the OP properly

2. I didn't know you could do that:
"#"="[book.xls]"

(although why "#" & "Sheet"?)

kudos

I wish I understood "why" the # is needed, but it's the part that makes the other sheet reference work as a hyperlink. Without it, the formula doesn't work. With it, it works and I can even copy down to get constantly working links.

Heh - read mine again - "#" = "[book.xls]"

So, within a workbook:
=hyperlink("sheet1!A1")
doesn't work
=hyperlink("[book1]sheet1!A1")
does work
=hyperlink("#sheet1!A1")
also works

That's what I meant by "#" = "[book.xls]"

Similarly, between books:
=hyperlink("[book1]sheet1!A1")
doesn't work
=hyperlink("C:etc.etc.[book1]sheet1!A1")
does work
=hyperlink("#[book1]sheet1!A1")

So the # seems to me like shorthand for 'in this workbook/in this folder'

Have I cracked it?

so the only one I haven't looked at is within sheet:
=hyperlink("A1")
doesn't work
=hyperlink("sheet1!A1")
doesn't work
=hyperlink("[book1]sheet1!A1")
does work (but this is as per within book but between sheets)
=hyperlink("#A1")
does work

So that doesn't quite follow the same logic - it would seem

My question:
(although why "#" & "Sheet"?)
I basically meant:
"#" & "sheet"
=
"#sheet"
That's all!

EDIT:
relative reference - that's the expression I was searching for when I wrote: 'in this workbook/in this folder' tip of my tongue it was! Cheers DO

# is as CC states a relative reference for the Hyperlink - though I confess I didn't realise it was relative to the highest level (ie folder - interesting)

Upload an example and we can write the exact formula for you, then you can work out what was missing.

Ummm, your formula works for me! Is this the problem:
http://support.microsoft.com/kb/829072
(if so follow instructions there)

In B2, then copied down:

=HYPERLINK("#"& "Details!" & ADDRESS(MATCH($A2, Details!$A:$A, 0), 1), "Link")

Oh yeah, gotcha, lol.

Based on above chat:

=HYPERLINK("#"& "Details!" & ADDRESS(MATCH($A2, Details!$A:$A, 0), 1), "Link")
=

=HYPERLINK("#Details!" & ADDRESS(MATCH($A2, Details!$A:$A, 0), 1), "Link")