Store maximum value in a cell

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.

Find more posts tagged with

Accepted answers

All comments

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!

franciscobt

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.