Hi,
I'm working on a spreadsheet that we use to record Supervisor safety contacts (when Supervisors have a "safety moment" with an employee). The contacts fall into different categories/topics (ie: PPE, egonomics, etc.).
Each department (packaging, production, etc.) has a different list of safety contact topics that they consider to be High Risk Topics.
What I'm trying to do is write a function that determines if the topic selected from the drop down list of available topics qualifies as a High Risk Topic, based on the Department selection (another drop down list).
Here is a mock-up of the relevant section:
<tbody>
</tbody>
I have lists of the high risk topics, sorted by department, located on a separate tab.
For example, these are some of the high risk topics for the Packaging department:
<tbody>
</tbody>
I get that I could use a COUNTIF or maybe a ISNUMBER(MATCH) function to look up the topic in a list of high risk topics. My issue is how do I change the lookup range based on the department selection.
Essentially I'm trying to write a function that says 'look for topic (B) in the range defined by department (A) and enter yes if there is a match or no if there isn't'.
Thanks for reading my post.
I'm working on a spreadsheet that we use to record Supervisor safety contacts (when Supervisors have a "safety moment" with an employee). The contacts fall into different categories/topics (ie: PPE, egonomics, etc.).
Each department (packaging, production, etc.) has a different list of safety contact topics that they consider to be High Risk Topics.
What I'm trying to do is write a function that determines if the topic selected from the drop down list of available topics qualifies as a High Risk Topic, based on the Department selection (another drop down list).
Here is a mock-up of the relevant section:
Employee Department | Topic | High Risk Topic? |
Drop down list (A) | Drop down list (B) | Yes/No |
<tbody>
</tbody>
I have lists of the high risk topics, sorted by department, located on a separate tab.
For example, these are some of the high risk topics for the Packaging department:
Packaging (department) |
Ergonomics |
Use of PPE |
<tbody>
</tbody>
I get that I could use a COUNTIF or maybe a ISNUMBER(MATCH) function to look up the topic in a list of high risk topics. My issue is how do I change the lookup range based on the department selection.
Essentially I'm trying to write a function that says 'look for topic (B) in the range defined by department (A) and enter yes if there is a match or no if there isn't'.
Thanks for reading my post.