How to prevent a TODAY() function from updating every time the sheet is opened

[swensor]

I have an inventory-type spreadsheet that inserts TODAY() in the date column whenever a new item is entered in a different column, "Item", which is handy. The problem is that every time the worksheet is opened, every cell in the "Date" column that has a corresponding "Item" get updated to today's date.


I want to have the date column populated once when a new item is entered in its corresponding item column, but never change again.


Any help would be much appreciated. Thanks.

 

[SirJB7]

Hi, swensor!


The only way is to set calculation mode to manual, so formulas won't change until you edit them or press F9 to recalculate or force recalculation by other methods. This as a general concept about calculation.


But regarding your question's environment, I should say that it's not suitable to insert TODAY() as a formula whenever a new item is entered. I don't know how do you do it, if it's by VBA code, change from formula to value so as to avoid further recalculations to change it; if its' by formula, you'll have to do tricky things like setting iteration calculations on and to a value of 2, and change formula to something like -for A1 cell example- =IF(A1="",TODAY(),A1).


Despite of this, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!