Steve_Brady
asked on
Combining =HYPERLINK() & =LOOKUP() in Excel
Hello,
This is a follow-up question to two other threads which can be found here:
1) Determining the correct argument for =HYPERLINK() in Excel
2) Understanding the syntax in =HYPERLINK() in Excel
In the two files attached to this question:
File1 has a some entries in col B under the heading "Num." Col C has the heading "Link" but no data yet.
File2\SheetA has entries in col E and col F under the headings "Num" and "Row" respectively
I have written a formula in File1 which works successfully to lookup data from File2\SheetA (it's off to the side in col F):
=LOOKUP(B3,[File2.xlsx]She etA!$E$2:$ E$15,[File 2.xlsx]She etA!$F$2:$ F$15)
in which:
the lookup_value comes from File1:col B
the lookup_vector is File2SheetA:col E
the result_vector is File2SheetA:col F
For simplicity, I will refer to this =LOOKUP() formula as L-Fxn.
Now, in File1:col C, under the heading "Link," I want to use =HYPERLINK() to create a column of links, for which the targets will be in File2\SheetB and have a cell reference in which the column is col A and the row is defined by L-Fxn. In other words, for any value in File1:col B, I want to be able to click an adjacent link in col C and have it take me to the col A cell of a row in File2\SheetB defined by the data in File2\SheetB (cell reference = A&L-Fxn).
Example:
File1:cell B3 value = 1629
In File2\SheetB, the value 1629 is found in cell B4
Cell C4 value = 604
Therefore, the link in File1:cell C3
should take me to File2\SheetB:cellA604
I've written plenty of =HYPERLINK() formulas in the past and I've tried several different changes to get it right but keep getting an error. I think the tricky part is plugging in L-Fxn with the correct quotation marks, etc. If someone could bail me out, I'd appreciate it.
Thanks
File1.xlsm File2.xlsm
This is a follow-up question to two other threads which can be found here:
1) Determining the correct argument for =HYPERLINK() in Excel
2) Understanding the syntax in =HYPERLINK() in Excel
In the two files attached to this question:
File1 has a some entries in col B under the heading "Num." Col C has the heading "Link" but no data yet.
File2\SheetA has entries in col E and col F under the headings "Num" and "Row" respectively
I have written a formula in File1 which works successfully to lookup data from File2\SheetA (it's off to the side in col F):
=LOOKUP(B3,[File2.xlsx]She
in which:
the lookup_value comes from File1:col B
the lookup_vector is File2SheetA:col E
the result_vector is File2SheetA:col F
For simplicity, I will refer to this =LOOKUP() formula as L-Fxn.
Now, in File1:col C, under the heading "Link," I want to use =HYPERLINK() to create a column of links, for which the targets will be in File2\SheetB and have a cell reference in which the column is col A and the row is defined by L-Fxn. In other words, for any value in File1:col B, I want to be able to click an adjacent link in col C and have it take me to the col A cell of a row in File2\SheetB defined by the data in File2\SheetB (cell reference = A&L-Fxn).
Example:
File1:cell B3 value = 1629
In File2\SheetB, the value 1629 is found in cell B4
Cell C4 value = 604
Therefore, the link in File1:cell C3
should take me to File2\SheetB:cellA604
I've written plenty of =HYPERLINK() formulas in the past and I've tried several different changes to get it right but keep getting an error. I think the tricky part is plugging in L-Fxn with the correct quotation marks, etc. If someone could bail me out, I'd appreciate it.
Thanks
File1.xlsm File2.xlsm
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It worked for me if I use Concetenate function.
jigneshthar,
i missed you comment while typing mine, sorry !
i missed you comment while typing mine, sorry !
No offense as this happens almost every other time with all of us :-)
Just wanted to highlight that I could see this working with single formula.
Just wanted to highlight that I could see this working with single formula.
ASKER
Thqanks!
LOOKUP(B3;'[path\File2.xls