Excel hyperlinks - Use of functions in hyperlink

[rusty]

I have tried using nested functions in a hyperlink to another cell on
different worksheet but keep getting the error that the specific fil
cannot be found.

Is it possible to use hypelink function with a nested function.

I'm trying to lookup a value in cell A1 on a second sheet in the
column and return the cell location so I can create a hyperlink to it.

Please help.
Thank

 

[Jon Peltier]

Rusty -

In stead of a hyperlink, you could use a macro that uses If-Then or
Select Case to go to another sheet or whatever, much as a hyperlink
would. Trigger the macro with a worksheet_selectionchange event
procedure, or if it's a drawing object, assign a macro.

- Jon

 

[rusty]

Jon,
Thanks for the help, but unfortunately my VB skills a very limited.
That's why I was hopping for the hyperlink option.

Might have to sit down for and pick up a VB book soon.

Thanks,
Rust

 

[Dave Peterson]

I could do this:
=IF(A1="a",HYPERLINK("#sheet1!a3"),HYPERLINK("#sheet2!a6"))

So I tried this:

I put a test table in A6:B8 that contained this:

a sheet2!a98
b 'sheet 3'!b23
c sheet1!c81

(just some values and ranges (with the sheet names)--notice the single quotes
around my 'sheet 3'--and I had to type a pair of them in the cell to start. One
was to indicate text and the second one was the "real" one.

Then I used this formula:

=HYPERLINK("#" & VLOOKUP(A1,A6:B8,2,FALSE))
And I put a, b, c in A1 and tested it. It worked ok.

If you can't get your formula to work, you may want to post what your data looks
like and the formula you're trying to get to work--no attachments, please.

 

[rusty]

Thank You Dave!

This worked great. Did exactly what I needed. I actually had all the
pieces, but not the "#" &. If you don't mind my query what does this
do?

The function I used was:

=HYPERLINK("#" & (ADDRESS(MATCH(A4,'FG Usage'!A:A,0),1,4,1,"FG
Usage")),"FG Usage")

Regards,
Rusty

 

[Dave Peterson]

I don't speak HTML, but it looks suspiciously like one of those subaddresses in
a hyperlink.

Then kind that take you to a spot on the same page in your web browser.