Function to Print a Date and Keep it in Column Until the Function is Satisfied Again


Hi All,

I'm working on a database sheet at present to track material deliveries in a lab. One piece of functionality that I require is the ability to hold a reorder date in a column as a notification to people if material in-house is due to expire. Below is how my sheet works:

  • As stock becomes low, a column called "Low Quantity Warning" is triggered and the stock is flagged as Low. A notification email is sent to the stock owner to kick off the restock process.
  • I have another column called "Status" which has 4 states: OK, Pending Reorder, Reordered, Received. By default it is in the OK state but when the low quantity is triggered I'd like to have it automatically changed to Pending Reorder. The problem is that I'd also like to give the user the ability to manually change the state to Reordered or Received, outside of the formula. Is this possible with a function?
  • Once the status is changed to Received and the item in-house, I have a date column titled "Last Received" with the function =IF(Status@row = "Received", TODAY()) to print todays date once that item has been received. I would like to lock this value into the cell now so that even when the status changes back to OK, Pending Reorder or Reordered, the previous data for the receipt of the item is still in place. One the item is received again in a few weeks or months time, I'd like that new data to be printed in the cell. Does anyone know of a method in doing this without creating many accompanying columns?



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!