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

  • Gia Thinh
    Gia Thinh Community Champion
    edited 01/27/25 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

  • Gia Thinh
    Gia Thinh Community Champion
    edited 01/27/25 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

  • 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

  • Gia Thinh
    Gia Thinh Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!