Collect Multiple Row Data into a Single Cell Reference

Options

Hi Smartsheet Community,

I am attempting to create a meeting planning set of sheets, and I am struggling with the following.

SITUATION:

Provide a formula for referencing a stakeholder register sheet for the name of a meeting attendee, and display their PTO in the cell. Please note that there will be multiple attendees for one meeting, and all PTOs must be displayed within the cell. Therefore, the stakeholder register will have one row for each stakeholder, but the meeting only has one row. This will automate a lot of the heavy lifting when determining meeting schedules when I only need to populate the PTO in one place.

SHEETS:

Stakeholder Register (Referenced Sheet) has the following columns: [Name] & [PTO & Other Exceptions]
Meeting Planner Sheet (Where formula will live): [Attendees] & [PTO & Other Exceptions]

ATTEMPTED FORMULA:

I tried using the following formula, but it does not successfully collect and display all the PTO's. It simply results in a blank cell.

=JOIN(COLLECT({PTO}, {Name}, (Attendees@row)), ", ")

HELP!:

Any ideas? It seems like the formula breaks down because there are multiple attendees within the meeting column, while each stakeholder has their own row within the Stakeholder register.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!