hyperlinks and vlookup

[jc52766]

Hi

I find hyperlinks can be a pain. You create 1 and if you change locations of things in a workbook you have to redo them. And other times they just break and you need to redo them.

So for my current spreadsheet i would save a heaps of time hopefully merging vlookup, hyperlink and cell (and maybe INDIRECT??) functions i assume to make a hyperlink find the reference cell itself hopefully.

So far i've tried things like:

=CELL("address",VLOOKUP(J3,'Validation with paperwork'!$J$3:$BN$91,1)) - But excel doesn't like this formula. Apparently the cell formula doesn't like vlookups within it.

I've tried some others with no success.

Is what i'm trying to achieve even possible without VB????

Basically i'm trying to vlookup a value in another tab. And find the reference cell to the HYPERLINK to for that value.

Thanks

 

[drsarao]

Hi

=CELL("address",VLOOKUP(J3,'Validation with paperwork'!$J$3:$BN$91,1)) - But excel doesn't like this formula. Apparently the cell formula doesn't like vlookups within it.

CELL() expects a reference (range) as second arg here. If what you have VLOOKUP() a text address then enclosing with INDIRECT() may do the trick.

I've tried some others with no success.

Have you tried HYPERLINK()

 

[jc52766]

FYI problem solved

I used the cell("address") in the far right of the tab i was going to HYPERLINK to.
I then use the hyperlink formula and reference this cell.

Problem solved. I don't have it with me at the moment but am happy to go into more detail if anyone needs any help.