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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|