Need Help creating a dynamic yearly report

Frequent Visitor

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 .

excel workbook