How to Update the File Name in a Formula concatenating to a cell?

[RAFARRAR]

I would like to make the date "8-23-2017" come from cell (say A1) that the user can choose, then substitute that cell into the external file string below.

Here's the formula in cell B1
='\\Page\08 Aug 1st Shift 17\[8-23-2017.xls]Daily Sch Prod Report'!A6

I thought this might work as follows, but no such luck.

='\\Page\08 Aug 1st Shift 17\&"["&A1&".xls]Daily Sch Prod Report'!A6"

Any Ideas? I see this can be done via a macro, but was wondering if this file name reference could be concatenated into a formula.

Thanks!

 

[alansidman]

Have you tried using the indirect function?

 

[RAFARRAR]

Indirect only works when the files are open. I have 15 files from different areas with the same date. I am looking at a way to change the date in a cell and concatenate that cell into the multiple file references.

Thanks!