hyperlink to value

[icestationzbra]

frank,

thanks for the reply.

here is the situation:

i use a macro to create hyperlinks. the hyperlinks show up a
Sheet2!A1. i then use a line in the same macro to truncate the !A1, an
the end result (visible) is Sheet2 (still a hyperlink). i would like t
be able to use that value in a formula. is that possible? now, thi
part of it needs to be done using a worksheet function only, as th
formulae might have to be changed depending on the requirement. peopl
not cognizant with macros might be wary of doing that. hence the nee
for a worksheet function to convert this hyperlink value to tex
value.

thanks in advance,

mac

 

[David McRitchie]

Would the following solve your problem ?
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)


Also look at
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyperlink
http://www.mvps.org/dmcritchie/excel/sheets.htm#indirect

='Worksheet 1'!G3
=INDIRECT("Worksheet 1'" & "!" & "G3")
=INDIRECT("'Worksheet 1'" & "!G" & h4)