Date based external reference

%3CLINGO-SUB%20id%3D%22lingo-sub-1826436%22%20slang%3D%22en-US%22%3EDate%20based%20external%20reference%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1826436%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20record%20our%20time%20weekly%20using%20a%20spreadsheet.%26nbsp%3B%20I%20have%20set%20it%20up%20so%20that%2C%20if%20we%20take%20leave%2C%20it%20increments%20a%20counter.%26nbsp%3B%20Each%20week%20use%20the%20save%20as%20command%20to%20start%20a%20new%20spreadsheet%20with%20the%20following%20naming%20protocol%20based%20on%20the%20week%20commencing%20date.%26nbsp%3B%20E.g.%20NAME%2026%20OCT%202020.%26nbsp%3B%20Next%20week's%20spreadsheet%20will%20therefore%20be%3A%20NAME%2002%20NOV%202020.%3C%2FP%3E%3CP%3EBy%20converting%20formats%20(dates%20to%20numbers%20etc)%20I%20can%20then%20generate%20the%20previous%20week's%20filename%20automatically%20using%20the%20Concatenate%20Function.%26nbsp%3B%20So%20I%20can%20display%20the%20following%20text%3A%3C%2FP%3E%3CP%3E%3D'C%3A%5CExcelchat%5C'%5BNAME%2019%20OCT%202020.xlsm%5DSheet1'!%24S%242%3C%2FP%3E%3CP%3EHowever%2C%20although%20this%20text%20is%20identical%20to%20what%20I%20would%20get%20if%20I%20open%26nbsp%3BNAME%2019%20OCT%202020.xlsm%20and%20type%20%3D%20into%20the%20appropriate%20cell%20in%20NAME%2026%20OCT%202020.xlsm%2C%20I%20can'%20make%20it%20link%20automatically.%3C%2FP%3E%3CP%3EWhat%20am%20I%20missing.%26nbsp%3B%20I%20have%20tried%20the%20Address%20and%20Indirect%20functions.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1826436%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Visitor

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