Cell reference to Workbooks that do not yet exist

[Fire_The_Torpedo]

Dear all,

I am creating a rota document that analyises and updates itself on a weekly basis.

At the moment it contains all the data from every day of the year so far. The rotas are created weekly, with names like:

w01 - Rota - 020119
w02 - Rota - 070119
w03 - Rota - 140119

and so on for 52 weeks.

It is fine collecting the data for all the previous weeks, but I want it to reference future weeks as well, that do not exist yet, so that when the workbooks are created and put into the correct location, it will reference them automatically once they are created, like:

w52 - Rota - 301219


I have tried with the following formula:

=IFERROR(INDIRECT('c:/Rotas/July/[w29 - Rota - 290719.xlsm]Monday'!$E12),$E$93)


The $e$93 is what I want the cell to show if the workbook is not yet created.


This formula doesn't really work. It keeps asking me to identify the workbook, which I cannot. It then shows what is in E93, but doesn't update even if I later create the workbook. It stays on the E93 reference.

Is there a forumla I can use that is happy to consider the location of the yet non-existant workbook, which will then automatically update once the workbook is created?

Thank you in advance, you guys always come through for me.

[AliGW]

It would be interesting to see your set-up. This to me sounds like something that PowerQuery might handle in terms of the data collation.