Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup results in 0 | Excel Discussion (Misc queries) | |||
Vlookup with two results | Excel Discussion (Misc queries) | |||
answer to odd results when comparing hyperlink addresses | Links and Linking in Excel | |||
Can I have mulitple web page results from a Hyperlink? | Excel Discussion (Misc queries) | |||
How do I annotate the cell results from Excel to a Hyperlink? | Excel Discussion (Misc queries) |