Filename in formula taken from a cell

[-emma-]

Hi,
I am looking for information to be pulled from another document - however - the document I want to link to will change often.
Ideally, what I want to be able to do is create the formula to look in a specific cell for the file name.
For example:

A1 = Emma.xlsx

B1 = '//DF.link2/Documents/[Emma.xlsx]Info'!D5
but what I want is for the File name to be whatever is displayed in A1 (the Emma.xlsx)

A few things to add - A1 may end up being a validation list at some point going forward, I am not sure if this will impact or not. Also I cannot use Marcos or VBA (work security settings).

If anyone could help me out on this, it would be fantastic! Thanks

 

[DanteAmor]

Try:

varios 22ene2023.xlsm
	A	B
1	emma.xlsx	some
Hoja1

Cell Formulas
Range	Formula
B1	B1	=INDIRECT("[" & A1 & "]Info!$D$5")

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

 

[-emma-]

Try:

varios 22ene2023.xlsm
	A	B
1	emma.xlsx	some
Hoja1

Cell Formulas
Range	Formula
B1	B1	=INDIRECT("[" & A1 & "]Info!$D$5")

If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

This works great - thanks!

I have however run into a little problem. One of the places I will be using this is a tab called "Info Complex" - the formula retuns a #REF! and the only reason I can see this happens is due to the space in the tab name.

Don't suppose there is a magic fix for this is there please? (Fingers crossed)

Thanks

 

[DanteAmor]

When there are spaces in the name, the formula must contain apostrophes.

Try:

=INDIRECT("'[" & A1 & "]Info'!$D$5")