A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

VLOOKUP RESULTS AS A HYPERLINK



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2008, 08:50 PM posted to microsoft.public.excel.links
takhan
external usenet poster
 
Posts: 1
Default 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

  #2  
Old May 29th, 2008, 01:55 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #3  
Old May 29th, 2008, 08:01 AM posted to microsoft.public.excel.links
takhan via OfficeKB.com
external usenet poster
 
Posts: 6
Default 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

  #4  
Old May 29th, 2008, 11:27 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #5  
Old May 29th, 2008, 11:29 PM posted to microsoft.public.excel.links
takhan via OfficeKB.com
external usenet poster
 
Posts: 6
Default 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

  #6  
Old June 1st, 2008, 11:47 AM posted to microsoft.public.excel.links
takhan via OfficeKB.com
external usenet poster
 
Posts: 6
Default 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

  #7  
Old June 1st, 2008, 11:30 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #8  
Old June 4th, 2008, 09:16 PM posted to microsoft.public.excel.links
takhan via OfficeKB.com
external usenet poster
 
Posts: 6
Default 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

  #9  
Old June 5th, 2008, 12:21 AM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default 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

  #10  
Old June 5th, 2008, 04:26 PM posted to microsoft.public.excel.links
takhan via OfficeKB.com
external usenet poster
 
Posts: 6
Default 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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.