Assign next document to next available person
I have a list of documents to be reviewed (thousands) and a list of reviewers (~40). I would like to auto assign the next available person on the list to the next available document for review. I have the below example sheet for documents to be reviewed and assigned to (I manually put in the assigned to items but will have another sheet with the info on the 40 reviewers which I anticipate at project start I may have to copy and paste their e-mail into “assigned to” to kick off the project), from another smartsheet the last date the reviewer submitted data and status of their review activities is queried into the status column and finally I have a formula that will show who is available for assignment (when their status shows complete). So for the sheet below I would like to get ‘b’ assigned to document ‘3b’, ‘d’ assigned to ‘3c’ and so on. Any ideas?
Best Answer
-
Hi @KevinMio8
Try the solution described below:
- Row# (text/number) : the row number of your People list (eg, 40)
- People List : A list of your 40 people
- Busy : Indicates if a person is already assigned
Column formula :=HAS(COLLECT([Assigned to]:[Assigned to], Status:Status, OR(@cell <> "Complete", @cell = "")), [People List]@row)
- Available List : Lists people who are not assigned,
Column formula :=IFERROR(INDEX(COLLECT([People List]:[People List], Busy:Busy, 0), [Row#]@row), "")
- Dynamic Row# : Identifies the row number of documents that are not assigned to anyone
Column formula :=IFERROR(IF(Document@row <> "", MATCH(Document@row, COLLECT(Document:Document, [Assigned to]:[Assigned to], ""), 0)), "")
- Candidate : Available people for assignment
Based on the "Candidate" column, you can :
- Manually enter a person into the "Assigned to" column, or
- Use the Assign People automation workflow to automatically assign people to the "Assigned to" column as shown below
Hope this helps.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Answers
-
Hi @KevinMio8
Try the solution described below:
- Row# (text/number) : the row number of your People list (eg, 40)
- People List : A list of your 40 people
- Busy : Indicates if a person is already assigned
Column formula :=HAS(COLLECT([Assigned to]:[Assigned to], Status:Status, OR(@cell <> "Complete", @cell = "")), [People List]@row)
- Available List : Lists people who are not assigned,
Column formula :=IFERROR(INDEX(COLLECT([People List]:[People List], Busy:Busy, 0), [Row#]@row), "")
- Dynamic Row# : Identifies the row number of documents that are not assigned to anyone
Column formula :=IFERROR(IF(Document@row <> "", MATCH(Document@row, COLLECT(Document:Document, [Assigned to]:[Assigned to], ""), 0)), "")
- Candidate : Available people for assignment
Based on the "Candidate" column, you can :
- Manually enter a person into the "Assigned to" column, or
- Use the Assign People automation workflow to automatically assign people to the "Assigned to" column as shown below
Hope this helps.
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech -
Hi Gia, This is a really nice solution. I tested it out and works up to the point of populating the candidate column - did you prepare a formula for the candidate column?
Thanks.
Kevin
-
Oh, I forgot to include this!
Candidate : Available people for assignment
Column formula :
=IFERROR(INDEX([Available List]:[Available List], [Dynamic Row#]@row), "")
Gia Thinh Technology - Smartsheet Solution Partner.
Email : thinh.huynh@giathinh.tech
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!