I've got some complicated conditional formatting to do. As I have software that adds data to one of the sheets I am only looking for a conditional formatting solution as to not brick the sheet for the future. Below I will explain whats on the sheet, what i'd like to have help with achieving and finally what I have thought about, tried and subsequently failed. To anybody reading this thanks.
Here I will describe whats on the workbook. To better understand you can look at the example sheet I have attached.
LogsThis is the sheet I am afraid of editing. Operations are added here. Column B shows what operator has performed an operation. Column D shows what equipment said operator used.
EquipmentThis sheet I am not afraid of editing as long as additions are added from column S and beyond. Here is a list of equipment. Column A has the equipment identifier. Column R shows what the minimum driving license level that is required to use said equipment.
OperatorsThis sheet I am not afraid of editing as long as additions are added from column J and beyond. Here is a list of operators. Column A has the ID of an operator. Column E shows what level license they have. Columns J-Q shows the license qualifications.
Here I will describe what I want to get done.
I want to add a conditional formatting which turns the cell red if the operator in question (column B) is not licensed to operate the equipment (column D). It is not as simple as performing a VLOOKUP on what the Operators license is and what the requirement for the equipment is. We need to keep in mind that a license might qualify you for several types of equipment.
Example 1: In row 4 in the "Logs" sheet we have "Alfons" which operated "Hyster E". "Alfons" has an "A4" license (shown in the "Operators" sheet) and "Hyster E" requires a minimum of a "B2" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B4 ("Alfons") to be filled red as he operated equipment outside of his license range.
Example 2: In row 2 in the "Logs" sheet we have "Ashley" which operated "Hyster A". "Ashley" has a "B2" license (shown in the "Operators" sheet) and "Hyster A" requires a minimum of a "A3" license (shown in the "Equipment" sheet). Then in the "Logs" sheet I would want the cell B2 ("Ashley") to not be filled as she operated equipment within her license range. Although "Ashley" doesnt have an "A3" license but her "B2" license qualifies her for operating "A3" equipment.
What have I tried?
I have tried using VLOOKUP to find the required license for the equipment and then using a mix of HLOOKUP and VLOOKUP to find in the grid J-Q in the "Operators" sheet if the license is TRUE or FALSE. I couldnt make it work properly.
If you have read this far thank you very much for spending the time reading this. I hope it has been clear enough to understand with the excel attachment.
Bookmarks