VLOOKUP - cell referencing to direct to a different Excel File

[Mark T]

Within a vlookup, I am trying to reference the text in another cell as the table_array.

E.g Cell A1 contains: '[filename]Sheet1'!$A1$:$Z$500
Cell B2 contains: May2016

What I'd like my formula to do in cell C5 - =vlookup(B2,A1,16)

So I'm trying to understand how to get my vlookup to understand the table_array that I am referring to in cell A1.

Thanks ... Mark

 

[candybg]

Is the file that you are referencing in cell A1 OPEN while attempting to run the formula?

 

[Mark T]

Is the file that you are referencing in cell A1 OPEN while attempting to run the formula?

No, but I do have the path and the filename in cell A1 ('[filename]Sheet1' was just an example)
The actual text in A1 is:
'F:\Projects\2016\[2016 Consolidated Forecast Summary Report May 2016.xlsx]Summary'!$A$7:$Q$595

 

[Khalid NGO]

Hi Mark,

If your file is open, you can use INDIRECT function.

If not, one way (not recommended)

INDIRECT will not work on closed files, you can use your filename and path as array with INDEX function, something like:

=INDEX('F:\Projects\2016\[2016 Consolidated Forecast Summary Report May 2016.xlsx]Summary'!$P$7:$P$595,MATCH(B1,'F:\Projects\2016\[2016 Consolidated Forecast Summary Report May 2016.xlsx]Summary'!$A$7:$A$595,0))

I have assumed your column number 16 is column P
and dates e.g. May2016 are in column A

or:

=INDEX('F:\Projects\2016\[2016 Consolidated Forecast Summary Report May 2016.xlsx]Summary'!$A$7:$Q$595,MATCH(B1,'F:\Projects\2016\[2016 Consolidated Forecast Summary Report May 2016.xlsx]Summary'!$A$7:$A$595,0),16)

Regards,