Formula Help: Choosing Most Recent Fields Across Sheets

Callie Perez

I’m working with two Smartsheet sheets:

• Sheet A: Tracks courses taught (each row has 2 instructors + 2 courses — both instructors taught both courses).
• Sheet B: Lists instructors. I want to display the most recent course(s) they taught, pulled from Sheet A.

My challenge: Because Sheet A has two instructor ID columns (Instructor 1 ID + Instructor 2 ID) and two course columns (Course 1 + Course 2), I’m struggling to figure out a formula setup that works across those 4 references.

Has anyone done something similar or have ideas for a formula that could help? Thanks so much!

Paul Newcome

Are you able to provide some screenshots (with fake data if needed) for context?

Callie Perez

Absolutely! Here's some sample info! So I need a formula that would pull the date that instructor ID last taught (whether he was in the ID-1 or ID-2 column) and the Courses Last taught (both 1 and 2).

I realize this could be made simpler by combining the two columns into one but old habits die hard, and I'm trying to figure out if I can support the team in a solution with this format that's working for them to track course history before requiring they do it differently.

Paul Newcome

You could use a helper column with a formula to combine the two into one. This leaves the input how they like it but also provides a format you already know how to work with. In fact my suggestion for the easiest route would be creating two helper columns (both multi-select dropdowns) with a formula to join the two columns each together.

=[Course 1]@row + CHAR(10) + [Course 2]@row

=[Instructor 1 ID]@orw + CHAR(10) + [Instructor 2 ID]@row

Then to bring over the date you would use a MAX/COLLECT/HAS along the lines of:

=MAX(COLLECT({Date}, {Instructor Helper}, HAS(@cell, [Instructor ID]@row)))

And to bring the courses over:

=INDEX(COLLECT({Course Helper}, {Instructor Helper}, HAS(@cell, [Instructor ID]@row), {Date}, @cell = [Date Last Taught]@row), 1)