Update a Symbol column based on another sheet's checkbox, whilst maintaining other information

celtics345

Okay, I have a three-part problem that I, in no way, can solve on my own.

Question 1

Above is a destination sheet we use that determines which operators are cleared on specific disciplines. We'd like to automate this based on the Cleared column in our main training source sheet (see picture below). When that checkbox is checked, that trainee's respective discipline column switches to the green "Yes" check. So, three criteria needs to be met (Cleared is checked, then we match Trainee and Discipline).

Question 2

That would be the first quandary to solve. Seems easy enough. The second one, however, seems a bit more complicated. With that functioning formula affecting all columns, is it possible to overwrite it so to speak, for operators already cleared on said functions? As you can see in picture 1, there's plenty of people who have the green check for various disciplines. I'd imagine, if we're able to solve Question 1, it'll negate those manual entries and only account for those who have Cleared checked. I'd like both conditions to be true. Current operators have their correct symbols reflected, while future operators have their symbol status updated when Cleared is checked.

Question 3

In a perfect-case scenario, we'd also like to include within this formula an indicator for if someone's currently training on said discipline. You can see this with the yellow "Hold" exclamation point in picture 1. Essentially, if Trainee is training on Discipline and Cleared is not checked on any row, then the symbol would be "Hold," switching to "Yes" when Cleared.

I know this is a lot, but you guys are geniuses here so I think we can figure it out. Any help would be appreciated, thank you!

Paul Newcome

Question 1:

You would use a COUNTIFS to count how many times that name appears on the same row as that discipline and where Cleared? is checked. Then you would say if the COUNTIFS equals zero then output "No".

Question 2:

You would need to either be selective of where you apply the formula or you would need to choose between automatic or manual. You cannot have both in a single cell. My suggestion would be automatic and then spend some time adding the existing people in to the source sheet.

Question 3:

What is your indicator for "In Training"? You would need to work that in to a nested IF alongside Question 1 above.

Paul Newcome

Question 1:

You would use a COUNTIFS to count how many times that name appears on the same row as that discipline and where Cleared? is checked. Then you would say if the COUNTIFS equals zero then output "No".

Question 2:

You would need to either be selective of where you apply the formula or you would need to choose between automatic or manual. You cannot have both in a single cell. My suggestion would be automatic and then spend some time adding the existing people in to the source sheet.

Question 3:

What is your indicator for "In Training"? You would need to work that in to a nested IF alongside Question 1 above.

celtics345

Hey Paul, thanks for the insight as always!

Question 1

I know it's easy stuff, but I really struggle to wrap my head around it as I have very limited experience in writing formulas. What am I missing here? Because I know it's something. It's returning an #INCORRECT ARGUMENT SET.

Question 2

Okay, that's what I expected. I believe putting all the data in the initial source sheet might be our way to go.

Question 3

If I can't get Question 1 I'm damn sure not going to be able to nest another IF statement inside it. But we'll cross that bridge when we get there. To answer your question, the indicator for somebody being "In Training" is that they do not have Cleared checked on any row. And in that case, the "Hold" symbol would be selected. Would that work?

celtics345

After looking into it more I think we'll actually stand down on the idea, particularly because Question 2 is imperative, and we'd have to add a lot of lines to the source sheet. I think overall it'll be easier to periodically do it manually than have it automated. But I think you for the help Paul, I really appreciate it!