Auto Assign Rotation

A Seb
A Seb ✭
inFormulas and Functions

Hi,

I am part of an application team. At any point, any one of us can take and work on any application request.

Requests are submitted via a form on our Application tracker sheet. I am looking to auto-assign someone to the next application entered either by who has the fewest open tasks or simply by rotating through the list. Right now there is only 2 of us actively taking on new applications.

I have attached a basic screenshot of what we're working with.

This sheet is driven by a form to auto populate at the bottom of the list with "Open" in the Status Column. The "ID" is auto populated by adding a number to the previous. This is done on a separate helper sheet.

Current Workflows are set to automatically move "Completed" tasks to a separate sheet.

Is there a way to do this without a helper sheet? Can I utilize helper columns in the same sheet and just hide them?

image.png


· Share on Facebook Share on Twitter

Answers

  • AravindGP
    AravindGP ✭✭

    Hi There,


    You can't really auto assign a request based on availability. However, a workaround could be to create an automation workflow to assign new requests by a parameter, for example, any requests for Application 1 can go to one person while Application 2 goes to another.


    Moving completed requests to a new sheet is the best way to have only work in progress or open items in your main task sheet. There's no option currently to hide rows. One suggestion I can think of for your ID column is to use the Auto Number column type instead of text.

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Enter to Win a FREE Conference Pass to ENGAGE 2023

 Learn more
@maineL<\/a>,<\/p>

I haven't seen this possible without the use of a 3rd party integration. I don't believe it is supported natively in Smartsheet. The question comes up all the time though. <\/p>

BRgds,<\/p>

-Ray<\/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":107090,"type":"question","name":"Calculate the # of months between 2 dates, with 1 date being static","excerpt":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12\/1\/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…","snippet":"Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my…","categoryID":322,"dateInserted":"2023-06-29T15:55:21+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T17:24:43+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":158227,"lastUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3376114804,"url":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107090\/calculate-the-of-months-between-2-dates-with-1-date-being-static","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":107090,"commentID":383163,"name":"Re: Calculate the # of months between 2 dates, with 1 date being static","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383163#Comment_383163","dateInserted":"2023-06-29T17:24:43+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T17:21:28+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-29T17:24:10+00:00","dateAnswered":"2023-06-29T17:11:25+00:00","acceptedAnswers":[{"commentID":383158,"body":"

Try a DATE function:<\/p>

=(NETDAYS([Requested Start Date]@row, DATE(2022, 12, 1)<\/strong>) \/ 365) * 12<\/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":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":107083,"type":"question","name":"Excluding data when using COUNTIF","excerpt":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…","snippet":"Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, \"Unallocated\") We do not want…","categoryID":322,"dateInserted":"2023-06-29T14:45:28+00:00","dateUpdated":null,"dateLastComment":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162412,"lastUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":25,"score":null,"hot":3376103408,"url":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107083\/excluding-data-when-using-countif","format":"Rich","lastPost":{"discussionID":107083,"commentID":383116,"name":"Re: Excluding data when using COUNTIF","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/383116#Comment_383116","dateInserted":"2023-06-29T15:24:40+00:00","insertUserID":162412,"insertUser":{"userID":162412,"name":"JennyB.","url":"https:\/\/community.smartsheet.com\/profile\/JennyB.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-29T15:20:21+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-29T15:24:21+00:00","dateAnswered":"2023-06-29T15:16:19+00:00","acceptedAnswers":[{"commentID":383110,"body":"

@JennyB.<\/a> <\/p>

If I'm understanding your question correctly, you want to counts from one column but want to exclude projects that have an \"On Hold\" status in a second column. If so, then a countifs formula would work:<\/p>

=COUNTIFS({Column to Count}, \"Unallocated\", {Other column}, <>\"On Hold\")<\/p>

Hope this helps!<\/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

  • Automatic Update of Dropdown List?

    I saw several discussions on this and I'm wondering if there is a formula or an easy way without a 3rd party to automatically update a dropdown list based on a cell in one sheet? I can create a helper sheet if that would help. Any ideas? I don't believe I have the premium Smartsheets plan. Thank you! Lori

  • Calculate the # of months between 2 dates, with 1 date being static

    Hi all, I am trying to insert a formula that will calculate the number of months (overlapping years) between two dates; 1) Requested Start Date, which is a data column on my sheet, and 2) 12/1/22 as a static date in every calculation. So far I have been able to create a basic formula that works, however, I had to create…

  • Excluding data when using COUNTIF

    Hi, I have a graph on a dashboard based on the status of a project using the following formula for each status: =COUNTIF({Optimisation List Range 2}, "Unallocated") We do not want to pull through any project that is on hold but want to keep the status as it is on this column so have an additional column with the RAG Status…