I am currently working on an excel workbook to schedule overtime for operations. The workbook already has formulas to correctly calculate hours for each employee, but the issue I've run into is having excel optimize where employees will be working. I have a feeling this is beyond just creating a spreadsheet and using macros, but here's to hoping.
My problem is essentially this, 6 operators run certain equipment on each shift, however they are not all trained on all of the equipment. For instance, Bob is trained on lines 1 and 2, while Gary is trained on lines 2 and 3. Only one of them is needed to run a line, so if Gary runs line 2 then Bob can only run line 1. I want to see if there is a way to make a macro or use solver to determine where each worker will be stationed. After that, I can use a macro to determine who will be needed for overtime, but I can't get past the initial portion.