Collect Multiple Row Data into a Single Cell Reference

KThordarson

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.

DKazatsky2

Hi @KThordarson,

This should give what you are after.

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

Hope this helps,

Dave

Lucas Rayala

Adding the extra parenthesis around Attendees@row likely threw it off. Try:

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

KThordarson

unfortunately that leaves me with "Incorrect Argument Set." :(

heyjay

=JOIN(COLLECT(PTO:PTO, Name:Name, CONTAINS(@cell, Attendees@row)), " , ")

KThordarson

so I dont get an error from your formula heyjay, but it doesnt display anything / leaves the cell blank

Andrée Starå

https://community.smartsheet.com/discussion/121813/collect-multiple-row-data-into-a-single-cell-reference

Hi,

I hope you're well and safe!

Can you share some screenshots? (Please delete/replace any confidential/sensitive information before sharing.) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

KThordarson

Hi All,

Here are some screenshots :). Thanks so much everyone!

Meeting Planner.png

Stakeholder Register.png

DKazatsky2

Hi @KThordarson,

This should give what you are after.

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

Hope this helps,

Dave

Andrée Starå

https://community.smartsheet.com/discussion/comment/436125#Comment_436125

Happy to help!

Glad you got it working!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!