|I have two separate issues: |
I have a list of employee names in column A.
I have their departments listed in column B.
I have their roles listed in column C.
I then have 52 weeks spanning from column D on wards.
The purpose of this sheet is to provide a forecast of competent employees for each week.
I mark a "C" for the employee that is forecast to become competent that week.
I am using the following formula to calculate the competencies forecast per week. This formula was working great.
=COUNTIFS(DEPARTMENT,"MELAMINE 3",ROLE,"IN-FEED OPERATOR",$D2:$D78,"C")+$E$39
$E$39 is calculating all of the employees already competent on another sheet. I must maintain the two separate sheets, as they both have a lot of individual requirements.
The problem I have now is, if we are currently in Week 1, and I mark an employee as being foreseen to become competent in Week 3, this calculates fine, but when I actually get to Week 3 and I update the second sheet of employees already competent, it is doubling the total of competent employees by using this formula.
To counteract this, I inserted a hidden row under the cell with the COUNTIFS formula in it and put this formula in the cell =IF($E$39>=Q$84, Q$84,$E$39)-Q$84. Q84 has the total of competent employees for that week from the other sheet.
This worked great, so I have been just summing the two formulated cells to give the accurate total of competent employees for that week, including all currently competent employees. The purpose of this total is to know the overall operational skilling per week, per department, per role, but not by employee.
So my question for issue 1 is: is there a way to combine both formulas, so I don't need to have hidden rows of formulas.
In relation to the same sheets. If we mark the employee as being "C" in say Week 50. We would prefer not to have to mark them "C" for the remaining weeks. Meaning once they are competent, the records for their row cease as they are no longer in training to become competent, and are therefore just a value in the total operational skilling for that week.
My question for Issue 2 is: is there a way to have Issue 1's formula calculating that if a "C" is placed in say Week 50, it calculates the employee as being competent in the remaining weeks without us placing a "C" in the remaining weeks. We are using a dropdown to enter the "C" into the cells for each employee, as there are other options not relating to the mentioned formula that we need to select as well.
I thought maybe something to say that if a C is selected from the dropdown, it enters a C in the remaining cells per week, but as white text to it cannot be seen. That way it will show the week they become competent as being "c", and nothing on the remaining weeks because their training is complete. However, the operational skilling is still correct as it is caclulating all the white C's.
Please help! Thank you.