Store maximum value in a cell

franciscobt ✭✭
edited 09/19/23 in Formulas and Functions

I have a spreadsheet that pulls the current stage (numbers 1-5) of several projects. If the project is terminated it goes back to 0. I want to be able to store the last stage reached before it was terminated (i.e. changed to 0). How could I do this?

In excel I would typically use a self-referencing cell, for example on cell B1 I would input the following formula =if(A1=0,B1,A1). So if the value of A1 is changed to 0, the current value of B1 is stored (i.e. last value of A1 before it was 0). This creates a circular reference which is not allowed in smartsheets.


  • Scott Peters
    Scott Peters ✭✭✭✭✭✭

    @franciscobt - I would recommend an automation for this. Create a column called "Previous Stage", and optionally, and Date column to go with it. I'm not sure how you terminate a project, but instead of having the stage be a formula set to zero, use an automation triggered when status changes to 'terminated', to first set the Previous Stage to be your current stage value, and then set your current stage value back to zero.

    Hope that's helpful!

  • Thank you for the answer.

    Another person terminates the project in another smartsheet that I do not control: basically changes a cell from say "3" to "0". So I want to be able to capture the "3" before it becomes a "0".

    Could I do this with an automation trigger?

  • Scott Peters
    Scott Peters ✭✭✭✭✭✭

    Yes, I would recommend creating 5 simple automations for each possible change (1-5), triggered whenever the stage is changed... but DON'T create an automation for when it changes to zero. Here's an example:

    However, this is a bit rudimentary, and only addresses the concept of retaining a value if Stage is set to zero. If your stages hop around more, and you need to record other scenarios (like jumping from 1 to 4, or going from 4 back to 2, etc.), we would need something more sophisticated.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!