How to use IF/AND/CONTAINS formulas with multiple sheets

Lisa McMunn

Hello,

I am having a bit of difficulty expanding an IF formula to include a value that is in another column. Let me give an example

https://app.smartsheet.com/workspaces/GJqhP3jxmV78WQfw4mc3c6PXH3WqPJcjM2c3Frh1

In this mock workspace I have created basically what I'm doing and what I would like to happen. (all names and ID numbers in this workspace are fictional)
In the Training Submissions Sheet, my trainers will enter ID numbers for Associates they train (It's not in this mock-up but, in the original I have an INDEX/MATCH formula in the Associate Name column that runs off of the roster for all active associates onsite to pull the name using the ID#)
What I need in the Associate Training List is a way to have it check the coordinating box in the row with matching ID and/or Name under whichever Training Title is added to the Submissions sheet under the same named title. I used this formula for the basics:

=IF(HAS({Training Submissions ID Number}, [Associate ID Number]@row), 1, 0)

As you can see on the sheet however, it's just checking all the boxes. What I can't seem to figure out is the wording to add to this formula to only check the box if the ID(or name) AND the Training Title matches the title of the column.

I really hope this all makes sense other than just in my head! Thank you in advance for any assistance!!! (Please let me know if the link above doesn't allow access to the workspace - Thanks!!)

Paul Newcome

Are you able to provide screenshots for context?

Lisa McMunn

Do these help? @Paul Newcome

Paul Newcome

I would suggest an IF/COUNTIFS combo. COUNTIFS to count how many rows have the ID and "Q1". If that COUNTIFS is greater than zero, check the box.

=IF(COUNTIFS({ID}, @cell = [Associate ID Number]@row, {Title}, @cell = "Q1") > 0, 1)

Lisa McMunn

I have added that formula to the column "Q1 Complete" on the Associate Training List. I'm not getting an error or anything but it is not checking the boxes for any submissions that have Q1 in the Training Title on the Training Submissions sheet.

Any advice on what I might have missed? Thanks in advance!

Paul Newcome

Make sure the searches contain exactly what you are using. It looks like the data in your sheet is "Q 1" with a space, but the formula is "Q1" without a space.

Lisa McMunn

@Paul Newcome Thank you for catching that! I was getting frustrated lol
It works perfectly now, thank you so much for the assistance!!!!

Paul Newcome

Happy to help. 👍️