cross-referencing between 2 worksheets

JEK

New Member
Joined
Feb 14, 2025
Messages
4
Office Version
  1. 365
Platform
  1. Windows
  2. Web
QuarterJob PositionObserved 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"
)
 
Something like this,
Excel Formula:
=IF(ISBLANK(D5),"",IF(OR(VLOOKUP($E5,PPERequirements!$A:$E,MATCH("Gloves Worn?",PPERequirements!$1:$1,0),FALSE)<>$F5,VLOOKUP($E5,PPERequirements!$A:$E,MATCH("Gown Worn?",PPERequirements!$1:$1,0),FALSE)<>$G5,VLOOKUP($E5,PPERequirements!$A:$E,MATCH("Mask Worn?",PPERequirements!$1:$1,0),FALSE)<>$H5,VLOOKUP($E5,PPERequirements!$A:$E,MATCH("Eye Protection Worn?",PPERequirements!$1:$1,0),FALSE)<>$I5),"non-compliant","compliant")))
Or
Excel Formula:
=IF(ISBLANK(D5), "", IF(COUNTIFS(PPERequirements!$A:$A, $E5, PPERequirements!$B:$B, $F5, PPERequirements!$C:$C, $G5, PPERequirements!$D:$D, $H5, PPERequirements!$E:$E, $I5) > 0, "compliant", "non-compliant"))
 
Upvote 0
Thank you for your reply! However, its still not working :(
When I use the first formula you provided it produces N/A. I tried a few adjustments to see if I could tell what the issue was but go no change when the following items were adjusted, one at a time, and returned prior to the sequential adjustment:
No change in forumlated when:
- row is blank
- boxes are filled as compliant
- cells are formatted as "general"
- cells are formatted as "text"
- Instead of "No" to mark items not required on PPERequirements chart, blank is used
- "No" used to mark items not required on PPERequirements chart
- row 5 of PPERequirements chart is compliant/matching data in row 5 of compliance worksheet

The second formula provided above produces "non-compliant" - same results when troubleshooting, in that there is no change from "non-compliant".
I feel like I'm missing something dumb that I should probably know because I've tried so many different formulas and have tried to get answers from 2 different AI programs, as well.
 
Upvote 0
Hi How are you! I like to to know, the above example you provided/ is that the actual format of the orginal one, AND can i see the sample "PPERequirements!" sheet AND the example table look like some query output, AND all i did was simply adjusted the structure of your formula without altering its functionality. Maybe if you can provide more samples, i guess we can try, TY
 
Upvote 0
Also your words say that your formula is in Column I but the formula you pasted in uses columns G to I as the Yes/No values which means the formula would eed to be in Column J. The test for D5 being blank indicates that the Lookup value used is in Column D but the formula uses E.
Please show an image that includes the row and column references to clarify the position of your data.
Also an image of the Lookup Table including the row and column references. (Do the column headings match ?)
 
Upvote 0
Sam, I used the "copy to clipboard" button on your formula box, and pasted that into cell I5.
Alex, column I5 will not have a yes/no value, it will (hopefully) populate with the "compliant" or "non-compliant". I'm not 100% certain I'm understanding your Column D/Column E statement but I just tried replacing "$E5" in the 1st formula with "$D5" and had no change in results.
I appreciate y'alls comments. I have taken a screenshot of all 3 worksheets referenced - including the one for data validation just in case.
 

Attachments

  • Sheet3_hidden standardized text.png
    Sheet3_hidden standardized text.png
    42 KB · Views: 2
  • Sheet2_PPERequirements chart.png
    Sheet2_PPERequirements chart.png
    50.8 KB · Views: 2
  • Sheet1_PPECompliance Tracker.png
    Sheet1_PPECompliance Tracker.png
    80.6 KB · Views: 2
Upvote 0
That is consistent with your data in the OP but not consistent with the formula you provided, @Sam_D_Ben also used the formula as the driver for what cells to use.

See if something like this works for you:
Excel Formula:
=LET(dataHdg,$E$1:$H$1,
dataHdgVal,LEFT(dataHdg,LEN(dataHdg)-1),
dataRow,$E5:$H5,
lkValues,INDEX(PPERequirements!$A:$E,MATCH($D5,PPERequirements!$A:$A,0),MATCH(dataHdgVal,PPERequirements!$1:$1,0)),
IF(SUM((lkValues=dataRow)+(lkValues<>"Yes")*(lkValues<>dataRow))=COLUMNS(dataHdg),"Compliant","Non-Compliant"))
 
Upvote 0
Thank you both very much for your advice. I have finally gotten a formula that works! I got rid of column D all together (this was only added due to input from other sources). I will post below if curious. Have a great day :)

=IF(B5="","",
IF(AND(
IF(INDEX('PPERequirements'!C:C, MATCH(B5&C5, 'PPERequirements'!A:A&'PPERequirements'!B:B, 0))="Yes", D5="Yes", TRUE),
IF(INDEX('PPERequirements'!D:D, MATCH(B5&C5, 'PPERequirements'!A:A&'PPERequirements'!B:B, 0))="Yes", E5="Yes", TRUE),
IF(INDEX('PPERequirements'!E:E, MATCH(B5&C5, 'PPERequirements'!A:A&'PPERequirements'!B:B, 0))="Yes", F5="Yes", TRUE),
IF(INDEX('PPERequirements'!F:F, MATCH(B5&C5, 'PPERequirements'!A:A&'PPERequirements'!B:B, 0))="Yes", G5="Yes", TRUE)
), "Compliant", "Non-Compliant"))
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top