I am not sure that I follow what you are saying. The table at the bottom of the Look Up Tables sheet shows whether or not there are points available for an employee regardless of the phase. I would suggest setting up a scenario that shows what you want and if the result isn't what you want post that particular question in a new thread (you could link back to this thread as a reference as to how you got to this point).
Continue with the explanations of the formulas using row 3 of the Dashboard sheet as attached to post #53:
The array entered formula* for Z3 (Trainings needed) is: The Sumproduct portion of the formula calculates the number of courses for a 2014 IT employee, in the Start phase, based on either mandatory or optional courses assigned and passed. The Index portion finds the number in O4 of the Look Up Tables sheet.
The formula for AC3 (Optional) is: The If portion determines whether or not the employee is pre 2016, if so the formula continues, if not it displays False. If the formula continues and either there are no optional courses offered or the employee passes at least one of the optional courses that is offered, the formula displays True. If there are optional courses offered and the employee doesn't pass at least one the formula displays False.
The array entered formula* for AF3 (Points) is: The If portion of the formula determines if the employee is 2016 or later and if so continues, if not it displays false. The Sumproduct portion calculates the points based on the table at the bottom of the 'Look Up Tables' sheet as well as row 3 on that sheet and row 3 on the 'Dashboard' sheet. The Index portion of the formula finds the number of points that employee should have to pass that phase.
The formulas for columns O, P and R are all similar to: The IF's are evaluated in turn so that once one evaluates to true the rest or ignored.
The formulas for columns Q and S are similar to: AND means that all arguments must evaluate to true in order to display 'N/A'. OR means that any of the arguments could evaluate to true in order to display 'Not OK'. If neither of the IF conditions evaluates to true the formula displays 'OK'.
I hope that this makes sense and is helpful.
If you have questions, I'd suggest starting a new thread to ask them.
I hope that you have a blessed day.
Bookmarks