Quarter | Job Position | Observed Task | [hidden] | Gloves Worn? | Gown Worn? | Mask Worn? | Eye Protection Worn? | Compliant? |
---|---|---|---|---|---|---|---|---|
[list: Q1; Q2; Q3; Q4] | [list: CRNA; ET/ST; MD; Nurse] | [list: task1; task2; task3; task4; task5; task6] | [formula: =C2&B2] | [list: Yes; No] | [list: Yes; No] | [list: Yes; No] | [list: Yes; No] | [forumla to populate "compliant", "non-compliant", or blank] |
The above is an example of a compliance tool that will allow managers to document workplace observations. Columns A thru G will be standardized by using data validation lists (standardized lists on on a seperate, hidden worksheet within the same workbook). I have create a seperate worksheet, within the same workbook, with a list of each position type, performing each different job task, and what PPE is required, using "Yes" for any required ppe.
I need column I to refer to this seperate worksheet2 when determining compliance, which it is currently doing with the formula provided below- however, if the employee is wearing additional ppe that is not required, this should not affect compliance (only lack of required items result in non-compliance); the formula below does not do this. The formula also returns "N/A" on blank rows, therefore I need to work in how to return a blank in I5 if D5 is blank.
I am beginner-level so there may be many better formulas to use for my needs, but currently this formula is used in I5 ("Compliant?" column):
=IF(
AND(
IF(ISBLANK(VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Gloves Worn?", PPERequirements!$1:$1, 0), FALSE)), FALSE, VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Gloves Worn?", PPERequirements!$1:$1, 0), FALSE) = $F5),
IF(ISBLANK(VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Gown Worn?", PPERequirements!$1:$1, 0), FALSE)), FALSE, VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Gown Worn?", PPERequirements!$1:$1, 0), FALSE) = $G5),
IF(ISBLANK(VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Mask Worn?", PPERequirements!$1:$1, 0), FALSE)), FALSE, VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Mask Worn?", PPERequirements!$1:$1, 0), FALSE) = $H5),
IF(ISBLANK(VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Eye Protection Worn?", PPERequirements!$1:$1, 0), FALSE)), FALSE, VLOOKUP($E5, PPERequirements!$A:$E, MATCH("Eye Protection Worn?", PPERequirements!$1:$1, 0), FALSE) = $I5)
),
"compliant",
"non-compliant"
)