O, great wizards of the Smartsheet community, hoping you can help me cast a formula spell.
Trying to create a formula to flag if a resource is overallocated in a master sheet that tracks resources across multiple projects. NOT using either the legacy resource management or RM by SS, just core Sheet functionality.
I managed to solve for overallocation on a single date, with:
=IF(SUMIFS([% Allocation]:[% Allocation], Resource:Resource, =Resource@row, Start:Start, =Start@row) > 1, 1, 0)
Which in practice does:
=if the sum of all [% Allocation] for the [Resource] and [Date] on the current row is over 100%, check the box.
Now I know this person is overallocated on this date, and can use conditional formatting to highlight areas that need review on the Gantt chart.
But, what I want is to capture if ANY dates within the current row's [Start] and [End] range overlap with any other row on the sheet (and sum the allocation). I don't need to return which specific dates in the range are an issue, just identify that these date ranges need to be reviewed with the checkbox.
ex: both of the below rows should be checked, because from Feb 14-28 Steve is 150% allocated.
Row 1: Steve is 75% allocated from 2023.02.01 - 2023.02.28
Row 25: Steve is 75% allocated from 2023.02.14 - 2023.03.15
Let me know if you have any ideas!
