Can you combine Hyperlink and Vlookup in Excel?

[Niall]

I have two spreasheets that have a common column what I want to do is be able
to click on a cell in the common column in the first spreadsheet so that it
will bring up the 2nd spreasheet but with the corresponding value that I
clicked on from the first spreadsheet. for eg. I would click on the value in
A2 and this would then search the corresponding column in the 2nd spreadsheet
for the matching value. Is this possible?

 

[Luke M]

Almost. Since the cell your are clicking on will contain the value (and I'm
assuming you want to be able to change this value, you really need two cells.
The first contains value (A1), the second contains this formula:

=HYPERLINK("[C:\My
Documents\Book1.xls]Sheet1!B"&MATCH(A1,'[Book1.xls]Sheet1'!$B:$B,0), "A1")

This formula will display the value of A1, but will open Book1, and try to
find the value of A1 in column B of Book1, and take you there. Note that it
will be easier to setup this formula if the other workbook is open at the
time (the MATCH function needs to be able to 'see' the workbook you want).
Modify the components as needed to fit your exact layout.

One final note: This formula contains no error checking. If the value in A1
is not found in column B, the workbook may open, but will not take you to
correct cell (obviously) and an error message will apear.