We record our time weekly using a spreadsheet. I have set it up so that, if we take leave, it increments a counter. Each week use the save as command to start a new spreadsheet with the following naming protocol based on the week commencing date. E.g. NAME 26 OCT 2020. Next week's spreadsheet will therefore be: NAME 02 NOV 2020.
By converting formats (dates to numbers etc) I can then generate the previous week's filename automatically using the Concatenate Function. So I can display the following text:
='C:\Excelchat\'[NAME 19 OCT 2020.xlsm]Sheet1'!$S$2
However, although this text is identical to what I would get if I open NAME 19 OCT 2020.xlsm and type = into the appropriate cell in NAME 26 OCT 2020.xlsm, I can' make it link automatically.
What am I missing. I have tried the Address and Indirect functions.