vanhunk
10-05-2012, 03:07 AM
Good day,
When I save the workbook as a new revision, I want to automatically change the date in a cell to the current date (i.e. the date the revision was changed). In other words I want this to happen automatically during the saving action.
The file name is "PSIM Model Tool Rev..." where ... represents the version. I have a formula in the spreadsheet that extracts the ... part of the file name.
The formula is:
=SUBSTITUTE((MID(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),21,LEN(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1))-24))," ",".")
The formula returns for example: "1.94", when the file is saved as "PSIM Model Tool Rev 1 94.xls".
A date is given in another cell as, for example: "01-Oct-12", if I now save the file as "PSIM Model Tool Rev 1 95.xls", the date in this cell must automatically update to today's date, i.e. to "05-Oct-2012", but stays as "01-Oct-12" when saved only (i.e. when the name of the file stays the same).
Thank you
When I save the workbook as a new revision, I want to automatically change the date in a cell to the current date (i.e. the date the revision was changed). In other words I want this to happen automatically during the saving action.
The file name is "PSIM Model Tool Rev..." where ... represents the version. I have a formula in the spreadsheet that extracts the ... part of the file name.
The formula is:
=SUBSTITUTE((MID(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1),21,LEN(MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1))-24))," ",".")
The formula returns for example: "1.94", when the file is saved as "PSIM Model Tool Rev 1 94.xls".
A date is given in another cell as, for example: "01-Oct-12", if I now save the file as "PSIM Model Tool Rev 1 95.xls", the date in this cell must automatically update to today's date, i.e. to "05-Oct-2012", but stays as "01-Oct-12" when saved only (i.e. when the name of the file stays the same).
Thank you