How to reference a cell in workbook 2 using a filename typed in a cell workbook 1

[knighttrader]

Hi

See attached screenshot.

I have two workbooks.
1. Data_Workbook.xlsm
2. Data_Analysis.xlsm

I want the value of '[Data_Workbook.xlsm]Sheet1'!$BP25 to appear in cell B4 of Data_Analysis.xlsm.

The name of the Data_Workbook.xlsm will be typed in cell B2 of Data_Analysis.xlsm.

Obviously I can type the Data_Workbook address directly into the formula in cell B4, however, I want the formula in cell B4 to use the workbook name typed in cel B2. E.g. '[B2.xlsm]Sheet1'!$BP25

Any idea if/how I can do that?

Many thanks

 

[shknbk2]

Try this in B4:

=INDIRECT("'["&B2&".xlsm]Sheet1'!$BP25")

 

[knighttrader]

Try this in B4:

=INDIRECT("'["&B2&".xlsm]Sheet1'!$BP25")

It worked with a slight modification. The file extension needs to be in quotes too, or removed.

=INDIRECT("'["&B2&".xlsm"&"]Sheet1'!$BP25")

Many thanks for the help.

 

[Peter_SSs]

The file extension needs to be in quotes too

You are correct, but you don't need so many part-strings and quote marks.

=INDIRECT("'["&B2&".xlsm]Sheet1'!$BP25")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[knighttrader]

You are correct, but you don't need so many part-strings and quote marks.

=INDIRECT("'["&B2&".xlsm]Sheet1'!$BP25")

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Okay, many thanks Peter.

I've updated my account as suggested.

 

[Peter_SSs]

I've updated my account as suggested.

Thanks for that.