I have a workbook that gets built every week "Project Status (Friday Date) the previous weekly status workbooks are saved on a network share.

I am trying to Dynamically change the workbooks name and retrieve Cell values from the previously saved workbook.

Previously saved Workbook: Project Status (20170701).xlsx
Workbook: Project Status (20170707).xlsx

each workbook is a copy of the previous weeks data

If you manually link the cell this is what it looks like:

='E:\Weekly Status\[Project Status (20170701).xlsx]"&"STATUS'!"$H4

while working on the current workbook file, the formula will get the previous weeks status from the saved workbook.
this date value is in cell $F$1

I have named fields "FileLoc" = the network Path

I tried this formula and about 50 others but get a #REF error.

in Cell I4 =(INDIRECT("'"&FileLoc&"[Project Status ("&$F$1&").xlsx]"&"STATUS'!"&$H4)
in Cell I5 =(INDIRECT("'"&FileLoc&"[Project Status ("&$F$1&").xlsx]"&"STATUS'!"&$H5)
in Cell I6 =(INDIRECT("'"&FileLoc&"[Project Status ("&$F$1&").xlsx]"&"STATUS'!"&$H6)
...... so on ......

Any Ideas what I am doing wrong?