Using cell value as filename to return a value from a closed workbook

[AndrewMB]

Good evening everyone,

I have a list of filenames in column A. The list is long. In another column I want a generic formula I can drag down that will return a value from the same cell in each of those files.

Currently, they are written manually like this;

=('P:\FORD BUSINESS UNIT\Quality\FORD SPC\SPC Analysis\eSPC\[SPC5260-02 cav2 Small Spig Seal Dia Lowest 201217.xlsx]Master'!$Y$3)

The filename in column A in this example would be; SPC5260-02 cav2 Small Spig Seal Dia Lowest 201217.


Is there a way of referencing the filename from column A? Ideally from the sheet but vba if needs be.

 

[JonXL]

This work?

=INDIRECT("('P:\FORD BUSINESS UNIT\Quality\FORD SPC\SPC Analysis\eSPC\["&$A1&".xlsx]Master'!$Y$3)")

 

[AndrewMB]

Thank you.

I know from reading, that INDIRECT will only work when the workbook is open, however, saying that, I can't even get that to pull a value in when it's open.

 

[JonXL]

What version of Excel do you use?

What is the value of A1?

 

[AndrewMB]

2013 Pro and SPC5260-02 cav2 Small Spig Seal Dia Lowest 201217

 

[JonXL]

If you remove the file path from the formula, does it work?

Also, how is it not working? Error? No result? Something else?

 

[AndrewMB]

If you remove the file path from the formula, does it work?

Also, how is it not working? Error? No result? Something else?

I'm getting a #REF ! error. Removing the file path does nothing.

 

[Fluff]

How about
=INDIRECT("'P:\FORD BUSINESS UNIT\Quality\FORD SPC\SPC Analysis\eSPC\["&$A1&".xlsx]Master'!$Y$3")