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):
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).
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.