INDIRECT nested in VLOOUP

Hi

I am trying to use cell as a reference to an external file name. I have a 'Sample Tracker" spreadsheet that is meant to look up multiple files for project details. I aim to enter the unique project ID and year and the tracker to do the rest using VLOOKUP and INDIRECT nested in VLOOKUP.

Here is an example formula from D2 in "Sample Tracker".

=VLOOKUP(A2,'[Project funding 2014.xlsx]Sheet1'!$A$2:$D$5,3,FALSE)

In the "Sample tracker" file I have an "Input" worksheet that allows me to put external files names in manually once.

Based on year I am able to look up the file name and display it column C using VLOOKUP. But in Columns D and E I would like to display contents of these external files without hard coding in the names but instead read the file name and if required table array from column C. I have been told I can use INDIRECT nested in VLOOKUP but I don't know how. How can I replace '[Project funding 2014.xlsx]Sheet1'!$A$2:$D$5 part of the formula with a reference to cells in column C.

Can anyone help?

Hi,

You can use INDIRECT function for this.

=VLOOKUP(A2,INDIRECT("'["&C2&".xlsx"&"]Sheet1'!"&"$A:$D"),3,0)

The values in column C should match the source file name & the files should be kept open for the formula to work (see the attached image).


I gave the file names for the source files as mentioned below & the formula worked for me:
Project Funding 2014
Project Funding 2015

Thank you so much. I am relatively new to complex formulas in Excel.

As I am trying to automate the tracker to minimise manual input I have another question. Worksheets names may change in the "Project Funding" files. +

How do I code in a worksheet name. Say I add another column, now column D and enter the worksheet name manually. How do I reference column D instead of Sheet1"! ?

Thanks.

Solved it. "&D2&"'!

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.