Auto Update of Source File Name

[Guest]

I am using a workbook that has many links to another file as below.
Unfortunately the source file name changes each day, with the new source file
name being based on its creation date. How can I enter a function that will
automatically update the file name (in bold) and takes into account that the
source file name will be based on the previous working days date.



='[041107.xls]Front sheet'!$H$10

 

[Guest]

Instead of ='[041107.xls]Front sheet'!$H$10

Let's put 041107.xls in cell A1 and then use:

=INDIRECT("'[" & $A$1 & "]Front sheet'!$H$10")


If we do this for all formulas having this kind of external link, then they
will all refer to cell $A$1 in the current worksheet. This means that
changing $A$1 will re-reference all your linked formulas at once.