Complex JOIN(COLLECT())

tcal780

Hello all,

I'm hoping to use a formula to return a JOINED list of shared USERS for all sheets in Sheet 1. I have three additional sheets that contain all the supporting data, but since I don't want group names, is it possible to perform the user lookups from the groups sheet within the COLLECT function?

Sheet 1: A list of Sheets, with each's containing Workspace. (sheetID, workspaceID, sheet name, workspace name)

Sheet 2: A list of Shares (users and groups) for all sheets included in Sheet 1. (shareID, sheetID, userID, groupID, email, User Name, Access, scope)

Sheet 3: A complete list of Workspace shares (users and groups). (shareID, workspaceID, userID, groupID, workspace name, User Name, email, access level, type)

Sheet 4: A complete list of Group members. (groupID, userID, group Name, email, firstName, lastName)

Thanks in advance

tcal780

I've decided to go with adding helper columns to each of the sheets, aggregating all the related users on each row. Then, referencing those columns in a collect formula on Sheet 1 to get all the required data.

Andrée Starå

https://community.smartsheet.com/discussion/comment/392159#Comment_392159

Hi,

I hope you're well and safe!

Excellent! Seems like a great solution!

Be safe, and have a fantastic week!

Best,

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

✅Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Summary checker

Looking to do something similar. Would a join/collect statement help with this? If, so, what it would it look like?

Andrée Starå

https://community.smartsheet.com/discussion/comment/393244#Comment_393244

Hi,

I hope you're well and safe!

Can you share some screenshots/mockups? (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!