Auto Allocation in Smartsheet based on criterias

saaadshaikhh · 05/19/24

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.

Paul Newcome · 05/20/24

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))))

Paul Newcome · 05/20/24

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 · 05/20/24

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