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

GSheehan

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?

Thanks.

Andrée Starå

Hi @GSheehan

Unfortunately, it's not possible at the moment to have a formula and manually change it as well, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

As a possible workaround, you could add so-called helper columns, one with the formula that shows the correct status and another one that can be changed manually.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

GSheehan

Thanks for the feedback Andree. I plan to hand the management of this system over to another group that have never used Smartsheet before so to ensure sustainability, I would like to keep the database and functionality as simple as possible and avoid helper columns.

Andrée Starå

@GSheehan

Happy to help!

How did it go?

Have a fantastic week & Happy Holidays!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD