Auto Allocation in Smartsheet based on criterias

saaadshaikhh

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

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

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

@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

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

saaadshaikhh

@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

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