Cross checking grids

mperez

Any help is appreciated. Also, if anyone can recommend which training/certification class would be better to deep dive into the formulas and workfflows for these types of specific tasks, I would love the recommendation.

I have Registration forms for 2 separate trainings (Training 1 Registration) and (Training 2 Registration); results go to their respective grids.

Reports are used to mark attendance using checkboxes to indicate checked in or no-show for respective Training (Training 1 Attendance) and (Training 2 Attendance).

Separately, there is (will be) a grid sent/uploaded each month containing the current months active roster with names and corresponding company name (Partner Rosters).

I would like to cross check the (partner rosters) list with the 2 separate training attendance reports (Training 1 and Training 2). If the name of the roster does not have a match on either attendance report I would like for them to dump into a separate report (Not Trained).

Thank you!

SoS | Dan Palenchar

Hello @mperez,

You can accomplish this with either (1) Cross Sheet formulas or (2) DataMesh.

1: Cross Sheet Formulas

I am assuming that "Partner" is the term for the people taking these trainings that you are evaluating.

The first steps are actually:

1. Making sure the Partner names are identically matched across Training 1 Attendance, Training 2 Attendance, and Partner Rosters.
2. Making sure Partner Rosters has a complete list of all Partners without duplicates.

Your question is not asking about these steps so I will assume this in order and move onto to the next part (see note below for some info on this if needed).

In Partner Rosters I would make 4 columns columns:

1. Partner > this is the name of each partner as they appear in the two training attendance Sheet (same column type),
2. Training 1 Attended > a checkbox column with a cross Sheet formula to query Training 1 Attendance for if the Partner checked into this trainings,
3. Training 2 Attended > a checkbox column with a cross Sheet formula to query Training 2 Attendance for if the Partner checked into this trainings, and
4. Both Trainings Attended > which will have a simple formula to see if both trainings have been attended. This will also be used for the report condition.

In Training 1 Attended use the below formula, and note cross Sheet references are in Brackets.

=COUNTIFS({Partner Name in Training 1 Attendance}, Partner@row, {Check in column in Training 1 Attendance}, 1)

Repeat this for the 2nd training in the same way.

Finally, in Both Trainings Attended use this formula

=IF(AND([Training 1 Attended]@row = 1), [Training 2 Attended]@row = 1), 1)

Make sure all of your formulas are column formulas. Both Trainings Attended will have a check for all Partners that checked in to both trainings.

Make a report and filter based on Both Trainings Attended being checked or unchecked to show those who have attended both trainings or not.

You could also use one report with grouping to show those trained vs. untrained.

You could also add one more column in Partner Roster and group people based on having done neither training, only training 1, only training 2, and both trainings.

2: DataMesh

The setup is the same here, the difference is that instead of using lookup formulas you would use DataMesh workflows for both Partner Training 1 and Partner Training 2. You would use the Partner as the identifier and then map the check in column in the Training Sheet to the Training # Attended column in Partner Rosters.

Trainings/ Certifications

You mentioned wanting training/ certification regarding formulas and workflows. The best way to learn this in my opinion is what you are doing - trying to build something yourself and using the Community and other online resources to help you out.

However:

• Smartsheet offers lots of free and paid training.
• School of Sheets offers training sessions where we work with you in video sessions to show you how to solve whatever problems you are working on. If you're interested in exploring this email me at dan@schoolofsheets.com.

Note on Duplicate Removal

If I was setting this up I would use a copy row automation to push the rows from the Training Attendance sheets to Partner Rosters to ensure an up to date list, however, I would also want to make sure there are no duplicate partners. If you want to set this up the video below will show you how to automate duplicate removals.

https://www.youtube.com/watch?v=i3DOqQXJXvk

mperez

@Dan Palenchar

Thank you so much! Reading through your response it looks like exactly what I am needing. I will let you know how it turns out. Thank you so much for taking the time to respond, I will definitely check out School of Sheets! 

Mario Perez