Save As Name To Change Cell Value
 

Greetings,

I am not sure if this question should be asked here or in
microsoft.public.excel.worksheet.functions. Please let me know if I
need to post this there as well. For now, this is the only group I
have posted this question.

I have a series of workbooks with the year and month as the file name
(eg: 2009-01.xls, 2009-02.xls ... etc.). I have this formula in a
cell to capture this new name and then anyplace where the date is
needed, I point back to this cell:

=DATE(MID(CELL("filename",A10),FIND("-",CELL("filename",A10))-4,4),MID(CELL("filename",A10),FIND("-",CELL("filename",A10))+1,2),1)

Seems to work except, I have to calculate the sheet before the results
update.

I could put a button on the sheet to manually calculate. However, I
am looking for an vba solution, if possible.

How can I get this formula to calculate after a save as event has
completed, to reflect the new date range (from the new file name) for
this work book?

Any help will be appreciated.

-Minitman