I am currently developing a portfolio management system in Smartsheet to centralize our work. To accommodate multiple project owners, we are utilizing a multi-select contact cell. When a user is assigned as an "owner," the initiative appears on their individual summary sheet. However, we have encountered a challenge: in summary reports for managers, we are unable to group by owner unless a single-select contact cell is used, which prevents us from accurately rolling up initiatives and calculating owner allocation percentages.
I have attempted a workaround using a helper column with a formula to extract the first name and last name initial of the owner for roll-up purposes. However, the formula only pulls information for the first contact listed in the multi-select contact cell and does not support additional owners.
Is there a way to automate the process so that, when multiple owners are listed in a parent row, the system creates 2 separate child rows for each owner and assigns the corresponding contact to each row? This would help ensure our reporting is accurate from both individual and managerial perspectives and allow each owner to make their own project updates in the required fields. Any guidance or recommended best practices would be greatly appreciated.
