Automatically Populate Status - Sales Pipeline

Itasca User

Looking for a way to populate a Status column (up, down, unchanged) depending upon update to a Stage field.

In English: If the entry in Stage changes, record in Status column for that record the DIRECTION of change. Example: Account currently in Stage B. Rep updates to Stage A today after conversation. Status should reflect UP (using the direction symbols in Smartsheet) since it went from "B" to "A."

Anyone done anything like this?

Jason

Ryan Kramer

@Itasca User ,

So given your example, you would need to track history of cells. There is a way to do this using the APIs. It would require some custom coding but basically, pull the cells that changed today, get get their current value and previous value, update the respective cell and you could either add a formula in your grid to evaluate the two cells or have your program make the comparison and lastly insert it into the given status column.

rough formula for generating the status columns A, B and C. On update of A, set the value of B to what A was previously. C then checks if A=1 and B=2 then "UP", if A=2 and B=1 then "DOWN" etc.

Let me know if you need some help!

Ryan

Andrée Starå

@Itasca User

Hi Jason,

Yes, I've developed something similar in many client solutions.

We can do this with Automations and a Workflow to copy the rows automatically to another sheet and have the information added/stored.

We'd then use a VLOOKUP or INDEX/MATCH formula to collect the information back to the sheet.

Make sense? Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post 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!

Ryan K

@Andrée Starå ,

I think his business case was to do it within the sheet. Not to move the records to another sheet.

To do it within the given sheet, I think you need the API integration.

Ryan

Andrée Starå

@Ryan Kramer

Yes, I know. My example doesn't move them to another sheet. We'd copy them to another sheet to lock in the values.

Make sense?

Ryan K

@Andrée Starå

I see. So before an update, insert the current value to a reference sheet and then reference that in the "prior status" field. It would work initially I think but would start getting clunky when there becomes many changes for a given record and your process would have to go through all records through a lookup each time and then there are the considerations on size of sheet. Ultimately you would be duplicating the data that is available from the API for a given cell history.

Right?

Ryan

Andrée Starå

@Ryan K

No, I don't think it would get clunky (depending on the specifics of the process, of course), and the size of the sheet would probably not be an issue either because soon we will have 20 000 rows available.

You're correct that we will be duplicating the data that is available with the API. Still, I think that if it makes sense for the process, it would be preferred to keep in Smartsheet without any add-ons or similar, and also, I think the investment of time/money would be less.

Ryan K

@Andrée Starå ,

Perhaps. It just depends on the total records being tracked. I have a few clients that will exceed 20,000 easily for a process like this. For a small process it may make sense.

To utilize the API for this would only take a day or so of custom development whereas managing this process would be cumulatively more time over the life of the process I think.

Ryan