VLOOKUP RESULTS AS A HYPERLINK
 

Gents,

I need your help please in creating a vlookup sheet.
I have got xl file with two sheets; lookup report and lookup log. I am trying
to show vlookup result as a hyperlink. column A on log has got some file
names which I am searching on report. the file names in log also hyperlink to
some files on network. I am trying this formula
=HYPERLINK(VLOOKUP(C15, 'LOOKUP LOG'!A2:Z1501, 1, 0)). Also does it matter
where this formula in report sheet should be typed in, as this formula is in
D4 in report sheet

Any idea what I am doing wrong????

Please help.

Many Thanks in advance

VLOOKUP RESULTS AS A HYPERLINK
 

Takhan wrote:
=HYPERLINK(VLOOKUP(C15, 'LOOKUP LOG'!A2:Z1501, 1, 0)).


This formula is equivalent to =HYPERLINK(C15) assuming the filename in
C15 exists in A2:A1501 on LOOKUP LOG. I doubt that is what you meant
it to do.

The third parameter of VLOOKUP is the column number within the table
that you want to take the result from, in the row containing C15 in the
first column.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

VLOOKUP RESULTS AS A HYPERLINK
 

Thanks for the info Bill, yes you are right everything is like what you have
said. I have used this formula and it also shows the file but it doesn't open
the file though the entry in log has got hyperlink and that works fine.

I do not know what I am doing wrong. any idea?

Thanks

Bill Manville wrote:
Takhan wrote:
=HYPERLINK(VLOOKUP(C15, 'LOOKUP LOG'!A2:Z1501, 1, 0)).

This formula is equivalent to =HYPERLINK(C15) assuming the filename in
C15 exists in A2:A1501 on LOOKUP LOG. I doubt that is what you meant
it to do.

The third parameter of VLOOKUP is the column number within the table
that you want to take the result from, in the row containing C15 in the
first column.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...links/200805/1

VLOOKUP RESULTS AS A HYPERLINK
 

=HYPERLINK(C15) (which is what your formula is equivalent to) will
create a hyperlink to whatever text is in C15.

I think you may be wanting the cell containing the formula to act as a
hyperlink to the same document as the hyperlink in the cell in 'LOOKUP
LOG'!A2:A1501 which contains the text that is visible in C15.

If so, the only way to do that would be via a VBA function.
Function GetHyperlink(R As Range) As String
If R.Hyperlinks.Count=1 Then
GetHyperlink = R.Hyperlinks(1).Address
If R.Hyperlinks(1).SubAddress<"" Then
GetHyperlink=GetHyperlink & "#" & R.Hyperlinks(1).SubAddress
End If
End If
End Function

Then in your cell you would have
=HYPERLINK(GetHyperlink(INDEX('LOOKUP LOG'!A2:A1501,MATCH(C15,'LOOKUP
LOG'!A2:A1501,0))))

(Partially untested)


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

VLOOKUP RESULTS AS A HYPERLINK
 

Thanks Bill,

I will try it tommorrow and will let you know

Many Thanks

Bill Manville wrote:
=HYPERLINK(C15) (which is what your formula is equivalent to) will
create a hyperlink to whatever text is in C15.

I think you may be wanting the cell containing the formula to act as a
hyperlink to the same document as the hyperlink in the cell in 'LOOKUP
LOG'!A2:A1501 which contains the text that is visible in C15.

If so, the only way to do that would be via a VBA function.
Function GetHyperlink(R As Range) As String
If R.Hyperlinks.Count=1 Then
GetHyperlink = R.Hyperlinks(1).Address
If R.Hyperlinks(1).SubAddress<"" Then
GetHyperlink=GetHyperlink & "#" & R.Hyperlinks(1).SubAddress
End If
End If
End Function

Then in your cell you would have
=HYPERLINK(GetHyperlink(INDEX('LOOKUP LOG'!A2:A1501,MATCH(C15,'LOOKUP
LOG'!A2:A1501,0))))

(Partially untested)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via http://www.officekb.com

VLOOKUP RESULTS AS A HYPERLINK
 

Hi Bill,

I have tried but it doesn't work. In the Cell it says Name$. It doesn't work.

Regards

Bill Manville wrote:
=HYPERLINK(C15) (which is what your formula is equivalent to) will
create a hyperlink to whatever text is in C15.

I think you may be wanting the cell containing the formula to act as a
hyperlink to the same document as the hyperlink in the cell in 'LOOKUP
LOG'!A2:A1501 which contains the text that is visible in C15.

If so, the only way to do that would be via a VBA function.
Function GetHyperlink(R As Range) As String
If R.Hyperlinks.Count=1 Then
GetHyperlink = R.Hyperlinks(1).Address
If R.Hyperlinks(1).SubAddress<"" Then
GetHyperlink=GetHyperlink & "#" & R.Hyperlinks(1).SubAddress
End If
End If
End Function

Then in your cell you would have
=HYPERLINK(GetHyperlink(INDEX('LOOKUP LOG'!A2:A1501,MATCH(C15,'LOOKUP
LOG'!A2:A1501,0))))

(Partially untested)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...links/200805/1

VLOOKUP RESULTS AS A HYPERLINK
 

Did you put the GetHyperlink function in a standard module?
(not ThisWorkbook!)?

It worked for me - I did test it.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

VLOOKUP RESULTS AS A HYPERLINK
 

Hi Bill,

Excuse for delay I was out for few days and wasn't around my PC.

No I haven't put the function in module. Sorry I dont know how to put it, I
am not familiar with VB.

Could you please guide me through how to do it.

Many Thanks



Bill Manville wrote:
Did you put the GetHyperlink function in a standard module?
(not ThisWorkbook!)?

It worked for me - I did test it.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...links/200806/1

VLOOKUP RESULTS AS A HYPERLINK
 

Alt+F11 to VB editor
Insert Module
Paste the code into the code window that will appear top right

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

VLOOKUP RESULTS AS A HYPERLINK
 

Hi Bill,
Thanks for reply
I have just tried it but still no luck.
I have done the VB function and then copied the formula you told me and in
cell it says #NAME?
=HYPERLINK(GetHyperlink(INDEX('LOOKUP LOG'!A2:A1501,MATCH(C15,'LOOKUP
LOG'!A2:A1501,0))))

Any thoughts.

Regards


Bill Manville wrote:
Alt+F11 to VB editor
Insert Module
Paste the code into the code window that will appear top right

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...links/200806/1

VLOOKUP RESULTS AS A HYPERLINK
 

Takhan via OfficeKB.com wrote:
I have done the VB function and then copied the formula you told me and in
cell it says #NAME?

Then you can't have done it right<g
It worked fine for me.

With the workbook open, Alt+F11 to the VB editor, in Project Explorer,
expand the project for your workbook (make sure it is not a different
workbook's project), double-click Module1 and check that the function is
indeed present there. If you don't have a Module1 then you didn't put it in
the right place.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

VLOOKUP RESULTS AS A HYPERLINK
 

Hi Bill,

I have put the info in module 1 and the formula in sheet but still it says
the same.

Regards

Ahmad

Bill Manville wrote:
Takhan via OfficeKB.com wrote:
I have done the VB function and then copied the formula you told me and in
cell it says #NAME?

Then you can't have done it right<g
It worked fine for me.

With the workbook open, Alt+F11 to the VB editor, in Project Explorer,
expand the project for your workbook (make sure it is not a different
workbook's project), double-click Module1 and check that the function is
indeed present there. If you don't have a Module1 then you didn't put it in
the right place.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...links/200806/1

VLOOKUP RESULTS AS A HYPERLINK
 

Send me the workbook (Bill underscore Manville at compuserve dot com)
and I'll take a look

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup