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

 

[London12F]

Hi Jame006,

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

If part of value of formula is copied, i.e. 'C:\Users\DK-01\Desktop\[File A1.xlsx]Sheet1'!$A$1
and add = at front, the cell is not #Ref and value appears.

 

[James006]

OK ...

Is this result what you actually expected or not ...???

 

[London12F]

Hi James006,

Your formula, = "'C:\Users\DK-01\Desktop\[" &A1& ".xlsx]Sheet1'!$A$1", can only give the result of
'C:\Users\DK-01\Desktop\[File A1.xlsx]Sheet1'!$A$1

but cannot give the value.

I have to copy (VALUE ONLY) and add = at front manually to give the value.

 

[James006]

Hi,

Is the file where you want to take the value from ... is already opened ...?

 

[London12F]

Hi James006,

The files are closed, as there are plenty of files that I want to extract their value of A1 only.

 

[James006]

Hi James006,

The files are closed, as there are plenty of files that I want to extract their value of A1 only.

Sorry ...

But we are back to square 1 ...

and you should use the solution provided in message # 2

Good Luck

 

[London12F]

Thanks James006.

I'll try myself.

 

[James006]

You are welcome