Automatically Populate Status - Sales Pipeline
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
Answers
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
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.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!