Excel Lookup to return value AND the underlying Hyperlink

[samato]

I have an Excel Worksheet in which Cell B2 the target. I wish to lookup the value that is in A2, in a table in a separate worksheet (List), have it return the value in the cell 2 columns over AND I want the Hyperlink that is in that cell to be a Hyperlink in the cell returned to. Did I explain that right?

Attached

 

[SkipVought]

Hi,

Is your hyperlink using the HYPERLINK() function?

I'm guessing that you'll need a VBA solution. In that case, how does the lookup value get into A1?

 

[samato]

The hyperlink was added by someone by right clicking on the cell and selecting "Hyperlink..." So Sheet1 has a single column A and it contains Project ID's; let's say 14-PRO-154 is in Cell A2. I want to look up this value in Sheet2 ColumnA and have it return the value in Column B (which is the project "name") AND return the Hyperlink that is underlying in that same cell; which was originally added with the method above.

 

[SkipVought]

So what's the functional need for the hyperlink? Doe it send you somewhere in the workbook? What is the purpose of this process?

 

[samato]

Not sure why that matters but it points to a word document on Sharepoint

 

[SkipVought]

Just trying to get a feel for your process.

So what would happen if you just used List sheet and set the AutoFilter?

 

[SkipVought]

Bottom line, if you want the hyperlink to be copied to Sheet1, you'll need event driven VBA code. It's pretty simple code, but your users will need to enable macros each time the workbook is opened for it to work as designed.