Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links
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   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
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   Report Post  
Posted to microsoft.public.excel.links
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



  #11   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default 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

  #12   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 6
Default 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

  #13   Report Post  
Posted to microsoft.public.excel.links
external usenet poster
 
Posts: 473
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
vlookup results in 0 sveazie Excel Discussion (Misc queries) 6 July 17th 07 09:20 AM
Vlookup with two results Luke Excel Discussion (Misc queries) 2 March 22nd 07 06:41 PM
answer to odd results when comparing hyperlink addresses Patricia Shannon Links and Linking in Excel 0 March 9th 06 08:28 PM
Can I have mulitple web page results from a Hyperlink? nastech Excel Discussion (Misc queries) 3 October 5th 05 04:05 PM
How do I annotate the cell results from Excel to a Hyperlink? kgagne Excel Discussion (Misc queries) 1 June 9th 05 05:49 PM


All times are GMT +1. The time now is 01:44 PM.

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

About Us

"It's about Microsoft Excel"