# Collect Multiple Row Data into a Single Cell Reference

edited 05/10/24

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.

Tags:

• ✭✭✭✭✭✭

Hi @KThordarson,

This should give what you are after.

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

Hope this helps,

Dave

• ✭✭✭✭✭✭

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

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

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

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

...

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

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• Hi All,

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

• ✭✭✭✭✭✭

Hi @KThordarson,

This should give what you are after.

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

Hope this helps,

Dave

• ✭✭✭✭✭✭
edited 05/16/24

Happy to help!

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!