Lookup Hyperlink and go to Address with Macro

**cfherd** · 02-07-2012, 11:10 AM

Sorry if this title is terrible, I am struggling to world exactly what I am looking for, probably why I can't find a solution anywhere else despite searching for the past 2 hours!

I currently have a VLOOKUP returning a URL and I have used the HYPERLINK function to format as a hyperlink as follows:

=HYPERLINK(VLOOKUP(A1,'Current Position'!$A$1:$C$2,3,FALSE)

I thought it would then be very simple to THEN use a macro from a button to then go to that URL however when I use the lookup the macro fails. Having the cell as just the plain URL without the LOOKUP works find, weirdly.

Here's the code I am using.

Please Login or Register  to view this content.

I assume there must be something wrong with the format of the cell i.e. it is not recognised as a Hyperlink?

Hope someone can help.

Thanks
Chris

**cfherd** · 05-10-2012, 11:18 AM

Sorry to bump but anyone have any ideas on this?

**ZealotAssasin** · 05-10-2012, 11:42 AM

Can you post your file? I did an example on mine and it worked fine.

**cfherd** · 05-10-2012, 11:58 AM

Thanks for the reply! Thought it should work...how do I post my file?

**ZealotAssasin** · 05-10-2012, 12:06 PM

When you press reply there's an icon with a paperclip and paper (Attachments) and there you can upload your Excel file.

**cfherd** · 05-10-2012, 12:08 PM

Hopefully this works!

**Leith Ross** · 05-10-2012, 12:54 PM

Hello cfherd,

There are a few preventing this from working. First the your named range "Current_Position" is not dynamic and second the "Search" button is a little more complex than you thought. Also the formula in B5 should be using "Current_Position"

Current_Position Formula

=OFFSET('Current Position'!$A:$C,1,0,COUNTA('Current Position'!A:A)-1,3)

B5 Formula

=HYPERLINK(VLOOKUP(B2,Current_Position,3,FALSE))

Search Button Code

Please Login or Register  to view this content.

**cfherd** · 05-11-2012, 04:24 AM

Works perfectly thank you! I need to add a little functionality around if the vlookup returns an #N/A or no agreement number is entered but I've done something like that before so I'll just try and adapt that to work with the code...I may be back!