Need Help creating a dynamic yearly report

I run a yearly report , and the information comes from different workbooks that are created on a weely bases, what I do basically is use Hlookup and then having to change the date of the workbook and sheet name manaully, it's very time consuming i need help to make it dynamic. I have tried to use indirect with no success ,  i made a video in order to try to explain what i do on regular bases.

Thanks a lot in advance for all the help I can get  to make this possible .

https://1drv.ms/v/s!AlsYRq2Fsn_QgZ8ueKeVNrB1FkAaRg