Reference External Workbook Using Cell Text

[amonty78]

I have a worksheet with a vlookup formula in cell F5 that references an external woorkbook as follows :

=VLOOKUP(E5,'C:\Users\Desktop\[2.xlsx]Sheet1'!$F$7:$G$10,2,FALSE)

As you will notice, this formula references an external workbook called 2.xlsx
However, instead of referencing 2.xlsx, I want it to reference the file name that is in cell D5. For example, if I type 7 in cell D5, then it will perform the vlookup on the file named 7.xlsx.

Hope this is clear and thanks for the assistance !

[GeoffW283]

The following should work for you.

Please Login or Register  to view this content.

7.xlsx will of course need to be open.

[amonty78]

Thanks very much for the reply that works, however is there a way that this can be done without opening the other file (7.xlsx), as this can be done with a normal vlookup ?

[GeoffW283]

Unfortunately the file needs to be open otherwise Excel has no way to get the data from the file.