Difficult Formula

[Mike]

Hello,

I have a spreadsheet that I want to put more automation into. It
contains the following cell pointer:

=+'T:\Accounting\Accounting Projects\ABC Project\Daily
Settlement\Settlement Sheets\2006-11\[Daily
Settlement_pvt_110806.xls]ABC Daily Settlement'!$H$35

The result is the contents of cell H35 in tab 'ABC Daily Settlement' in
workbook 'Daily Settlement_pvt_110806.xls'. Currently I have to
manually edit the cell to update the file name to reflect a new date,
e.g., from 'Daily Settlement_pvt_110806.xls' to 'Daily
Settlement_pvt_112006.xls'. Is there a way for me to automate the date
change? I already have one range where the date is input and it updates
all the dates in the spreadsheet but I have not been able to figure out
how to accomplish that with this cell pointer. I have alrady tried
=REPLACE and =SUBSTITUTE to no avail so any assistance anyone can
provide will be greatly appreciated!

Mike

 

[Guest]

Try using INDIRECT, i.e. if you have the relevant date (in any date format)
in cell A1 as the same worksheet with your formula, use

=INDIRECT("'T:\Accounting\Accounting Projects\ABC Project\Daily
Settlement\Settlement Sheets\2006-11\[Daily
Settlement_pvt_"&TEXT(A1,"mmddyy")&".xls]ABC Daily Settlement'!$H$35")

 

[Pete_UK]

Is the file likely to be open at the same time? I doubt it from what
you have written, but if it is (or if it could be) then you could use
the INDIRECT function - this only works with open files, though.

Hope this helps,

Pete