How to set dynamic formula to get value from list of filename?

[London12F]

There is a list of filenames at Column A, say File A1, File A2, File A3 ...
How to set the formula at Column B so that it can get the value of A1 of files
i.e. Cell B1 can get the value of A1 for filename at Cell A1;
Cell B2 can get the value of A1 for filename at Cell A2;

NB: the path for the value of A1 of File A1 is:

='C:\Users\DK-01\Desktop\[File A1.xlsx]Sheet1'!$A$1

 

[James006]

Hello,

From reading your objective, you seem to have the objective of getting values from a list of workbooks ...

But all these workbooks are closed ... and more importantly ... you have no intention to open them to retrieve your data ....

If that is the case, John Walkenbach has created a very interesting trick :

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/

Hope this will help

 

[London12F]

Hi James006,

Thanks for your link.
How about not using VBA?

 

[James006]

Hi,

Do you mean you only need a formula ...???

 

[London12F]

Hi James006,

Yes, I need formula at column B,

e.g. cell A1 = File A1;
formula at B1 = "'C:\Users\DK-01\Desktop\[" & A1 & ".xlsx]Sheet1'!$A$1"
cell A2 = File A2;
formula at B2 = "'C:\Users\DK-01\Desktop\[" & A2 & ".xlsx]Sheet1'!$A$1"

 

[James006]

Hi again,

Formula in cell B1 = "'C:\Users\DK-01\Desktop\[" & Indirect(A1) & ".xlsx]Sheet1'!$A$1"

Hope this will help

 

[London12F]

Hi James006,

Still not work.

 

[James006]

What does not work ...????

What is the output of cell B1 .... once you input the formula ...???

 

[London12F]

Hi Jame006,

The cell become #Ref .

 

[James006]

OK ...

Can you tell what following formula produces at your end ...

= "'C:\Users\DK-01\Desktop\[" &A1& ".xlsx]Sheet1'!$A$1"