Volatile Function To Update Or Refresh Only Once

[StevenC]

Good Day,

If somebody can please assist, and point me in the right direction. I am using the Now() to get the current date and time, I then format the date and time to a string, and use it to create a unique reference for the record.

The problem comes in when the user saves the file, and opens it again it updated the time and date causing the reference to change.

Is there anybody that can point me in the right direction to sort this problem.

[Excel Fox]

If you are using macros to create the unique reference, you could use macro to populate the cell with current datetime value and then use it as unique reference. If you do not want to use macros, there is a work around, but you must understand that this is an application level workaround, which means that it could affect other workbooks that are open.

The NOW function is volatile in nature. That's why it calculates everytime the file is saved or reopened. To overcome this issue, try this..

Enable Iteration (http://office.microsoft.com/en-us/excel-help/remove-or-allow-a-circular-reference-HP010066243.aspx#BMmake_a_circular_reference_work_ by_cha) in your application (this is the work around I was suggesting)

And use this formula in A1

=IF(A1=0,NOW(),A1)

[StevenC]

Thanks you, I tried the Enable Iteration, but as this sheet will be distributed to various users, and as its a setting on the excel, not the sheet it does cause a problem.So its back to the drawing board. Was thinking of create a macro that saves the data to a seperate file, which then does not have all the macros and formulas.