Formula to convert a Cell Value to a File link/lookup

[Bostiao]

Hi guys, appreciate any help please.
The File Name inserted at B1
I would like a formula in B2 to look into the File "11111111 GEB25 Trial 1" and return the value in Cell AJ17
If I would enter the formula/link manually it would be ='[11111111 GEB25 Trial 1.xlsm]Work Book'!AJ17
However, I would like the formula in B2 to return the above formula whatever the file name value in B1 is.
Thank you
Bostiao

 

[Legacy 143009]

Like this?

=INDIRECT("'["&$B$1&".xlsm]Work Book'!AJ17")

 

[Bostiao]

Great, thanks Flash.

Is there a way around to get this to work if the source file is closed?

 

[Legacy 143009]

Noo.. I don't think so.. Offset and Indirect are volatile functions which can not store values when you close the workbook. They need to recalculate each time, so it always needs the source to be open.

Maube you should wait for other solutions.

 

[Fluff]

Is there a way around to get this to work if the source file is closed?

Fraid not, the only way is Indirect & as already said it doesn't work on a closed workbook.