Difficult Formula
 

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

Difficult Formula
 

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")


"Mike" wrote:

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

Difficult Formula
 

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

Mike wrote:

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