Date based external reference

Copper Contributor

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.

0 Replies