Problem with equation to link to another workbook

[ElephantEars]

I have an equation shown below:

='H:\Business\Excel Data\Weekly Occup Reports-Other Prop\2016\[2016 wkly occup v2_Compute.xls]Jun 19'!$E$10



Instead of using the Jun 19 sheet each time, I need the sheet name to be pulled from Cell A1 on this workbook,
so cell A1 would be "Jun 26" next week.


I have tried Indirect, but the sheet would need to be open.
I have tried Concatenate but have had problems with it also.

I am sure I am missing something simple but I can't seem to figure it out

Thank you for any help with this.

 

[Chrisdontm]

I recently did a project with the very same problem.
I finally scrapped it because of the sheet needing to be open. ( Using Indirect )
I thought to approach it again, but this time with a macro.
Basically having the macro run over and open it and get the info, and close it back.
( I haven't had time to try it yet, but I believe that will be what needs to be done, hence xlsm or xlsb )
Good luck!