Auto Allocation in Smartsheet based on criterias

Options

Hi, I am looking to build some sort of formulation which will auto allocate lines to an individual, if the said criteria is met.

I have a sheet which lists down, Employee Name and Coverage, both of which can be a duplicate. However the concatenated value of both of these will always be unique, since there will always be one entry per employee per coverage.

I have a main sheet which will have coverages listed in one column. Now the ask is, for every coverage listed in this column, it should look for the coverage in the above sheet, and then from the matching employee entries, assign one by one. This does not need to be necessarily even, just a round robin sort of format should work.

Could somebody potentially help me with a formula here? I'm thinking something around an auto number here, but stuck with the output.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/20/24 Answer ✓
    Options

    If you insert an auto-number column (called "Auto" in this example) with no special formatting, you can use something along the lines of

    EDIT: Updated formula.

    =INDEX(COLLECT({Reference Sheet Employee Name Column}, {Reference Sheet Coverage Column}, @cell = Coverage@row), IF(MOD(COUNTIFS(Coverage:Coverage, @cell = Coverage@row, Auto:Auto, @cell <= Auto@row), COUNTIFS({Regerence Sheet Coverage Column}, @cell = Coverage@row)) = 0, COUNTIFS({Reference Sheet Coverage Column}, @cell = Coverage@row), MOD(COUNTIFS(Coverage:Coverage, @cell = Coverage@row, Auto:Auto, @cell <= Auto@row), COUNTIFS({Reference Sheet Coverage Column}, @cell = Coverage@row))))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/20/24 Answer ✓
    Options

    If you insert an auto-number column (called "Auto" in this example) with no special formatting, you can use something along the lines of

    EDIT: Updated formula.

    =INDEX(COLLECT({Reference Sheet Employee Name Column}, {Reference Sheet Coverage Column}, @cell = Coverage@row), IF(MOD(COUNTIFS(Coverage:Coverage, @cell = Coverage@row, Auto:Auto, @cell <= Auto@row), COUNTIFS({Regerence Sheet Coverage Column}, @cell = Coverage@row)) = 0, COUNTIFS({Reference Sheet Coverage Column}, @cell = Coverage@row), MOD(COUNTIFS(Coverage:Coverage, @cell = Coverage@row, Auto:Auto, @cell <= Auto@row), COUNTIFS({Reference Sheet Coverage Column}, @cell = Coverage@row))))

  • saaadshaikhh
    saaadshaikhh ✭✭✭
    edited 05/20/24
    Options

    @Paul Newcome

    Thanks for this. While i try this out, what does @cell signify here? I am not able to understand the role of @cell. Is it the cell number?

    EDIT: I was able to try this out, this is what I built. However, gives me invalid argument on this. What am I missing here?

    =INDEX(COLLECT({Competency Tracker Employee Name}, {Competency Tracker Coverage}, Coverage1 = Coverage@row, IF(MOD(COUNTIFS(Coverage:Coverage, Coverage1 = Coverage@row, Auto:Auto, Auto1 <= Auto@row), COUNTIFS({Competency Tracker Coverage}, Coverage1 = Coverage@row)) = 0, COUNTIFS({Competency Tracker Coverage}, Coverage1 = Coverage@row), MOD(COUNTIFS(Coverage:Coverage, Coverage1 = Coverage@row, Auto:Auto, Auto1 <= Auto@row), COUNTIFS({Competency Tracker Coverage}, Coverage1 = Coverage@row)))))


    Reference Sheet:

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @cell should stay exactly as it is in my example.

  • saaadshaikhh
    saaadshaikhh ✭✭✭
    edited 05/21/24
    Options

    @Paul Newcome

    Edit: You are a genius! It works perfectly now. Thank you for the help. Can you tell me the significance of @cell ?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @cell simply tells the formula to evaluate the previously established range on a cell by cell basis.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!