Referencing a cell from an external file using a cell reference...

[Rick_S.]

Hi folks,

My primary spreadsheet cell B13 references a cell in a separate file in this manner:
='C:\Rick\__Transportation\Bill\Budget_Billing\2020-2021\[34_23-903-6121_Overtime__2020-2021.xlsx]Summary'!$E$2 - this works as expected.

What I would like to be able to do is substitute the contents of cell A13 which contains: "34_23-903-6121_Overtime__2020-2021" concatenated with &”.xlxs“ in place of
the 34_23-903-6121_Overtime__2020-2021.xlsx contained inside square brackets in the formula shown above.

I have not been successful and there are 400+ more occurrences where I need to do a similar action.

Any and all recommendations welcomed.

Much appreciated & till later,
Rick S.
San Antonio, TX

[AliGW]

Welcome to the forum.

Try this:

=INDIRECT("'C:\Rick\__Transportation\Bill\Budget_Billing\2020-2021\["&A13&".xlsx]Summary'!$E$2")

[Rick_S.]

Hi Ali,

Thank you for such a fast response !!

With regret, it did not work; I get a #REF! error - had this error message many times when I tried other formula related tricks. I did a cut/paste from your reply to ensure no typos... but to no avail.

Hope all is well & till later,
Rick S.