Hello,
I have a sheet for users to enter requests for our quality department. They chose a type of task they need, i have a VLookup assigning how many hours to assign to that row based on the task. That all works fine. But I need to find away to factor in the staffing restraints for each day and push some tasks out if too many others are already due that day. I have created a grid but can't use a gantt b/c I'm using formulas for duration and start/end dates.
I have a quantity of hours available for tasks per day - and what i want is for SmartSheets to be able to look at the date its available to work on, consider how many "unassigned hours" are available for that day, and then if unassigned hours>hours needed for task, then return that date under an Assigned column, and then reduce the number of hours available for that day. If Day 1 doesn't have enough unassigned hours, then look at Day 2 -etc.
I created the columns above to do that - it refences this separate grid below that sums the items with the "planned date" for each day along with the available hours.
The tricky part is turning out to be that i want it to reduce the number of "unassigned" hours without creating a circular reference, and that i want it to consider each row separately - currently it seems to be considering all things due on a date together (summed).
Trying to allocate work based on available hours doesn't seem like it would be a unique problem - has anyone else figured out a way to do this? Hopefully a simpler way? Would the Resource Management Add-On do this for me?
Thanks in advance!
