Automatically Populate Status - Sales Pipeline

Looking for a way to populate aStatuscolumn (up, down, unchanged) depending upon update to aStagefield.


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


Anyone done anything like this?


Jason

Answers

  • Ryan Kramer
    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å
    Andrée Starå ✭✭✭✭✭✭

    @Itasca User

    Hi Jason,

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

    We can do this withAutomations and a Workflowto 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将它标记为接受答案/有帮助. 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:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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å
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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å
    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.

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • @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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Hey @Marcus Halvorson<\/a> <\/p>

Try this<\/p>

=IF(Available@row > 0, \"PES\", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,\"6633112443\")), 1)))<\/p>

Will this work for you?<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106579,"type":"question","name":"Split Text","excerpt":"Can someone assist me? I need a formula on how to split the text for this below. Ex. LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410 I need a to have a column that would write MEGA MALL only.","categoryID":322,"dateInserted":"2023-06-17T17:04:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-18T01:39:56+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-06-18T01:41:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3374075691,"url":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","format":"Rich","lastPost":{"discussionID":106579,"commentID":381096,"name":"Re: Split Text","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381096#Comment_381096","dateInserted":"2023-06-18T01:39:56+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-18T01:42:10+00:00","dateAnswered":"2023-06-18T01:39:56+00:00","acceptedAnswers":[{"commentID":381096,"body":"

Hey @Marilen.Navarro103391<\/a> <\/p>

Please use the formula I provided. It was intentional for the character \"~\" to be included. <\/p>

=MID(Name@row, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)) + 1, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 6)) - 1 - FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)))<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":106483,"type":"question","name":"Cross Sheet formula from two different columns","excerpt":"Hi Friends, I am currently using sheet A that has its rows automatically filled up by bringing the value from another sheet B using the VLOOKUP function. For example, I manually fill up \"BIM ID\" column in sheet A and hence the \"SO\" column in sheet A is filled up automatically by cross-checking sheet B using the formula:…","categoryID":322,"dateInserted":"2023-06-15T08:43:02+00:00","dateUpdated":null,"dateLastComment":"2023-06-19T03:57:12+00:00","insertUserID":139197,"insertUser":{"userID":139197,"name":"Yousef J.","url":"https:\/\/community.smartsheet.com\/profile\/Yousef%20J.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T03:57:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":139197,"lastUser":{"userID":139197,"name":"Yousef J.","url":"https:\/\/community.smartsheet.com\/profile\/Yousef%20J.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T03:57:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":62,"score":null,"hot":3373969214,"url":"https:\/\/community.smartsheet.com\/discussion\/106483\/cross-sheet-formula-from-two-different-columns","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106483\/cross-sheet-formula-from-two-different-columns","format":"Rich","lastPost":{"discussionID":106483,"commentID":381115,"name":"Re: Cross Sheet formula from two different columns","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381115#Comment_381115","dateInserted":"2023-06-19T03:57:12+00:00","insertUserID":139197,"insertUser":{"userID":139197,"name":"Yousef J.","url":"https:\/\/community.smartsheet.com\/profile\/Yousef%20J.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-19T03:57:38+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-19T03:57:35+00:00","dateAnswered":"2023-06-16T14:12:41+00:00","acceptedAnswers":[{"commentID":381013,"body":"

I haven't dived in deep but one thing I noticed is on the first reference you have two sets of {} this can give an error.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions