Is this possible in Smartsheet?

Orlando Mercado

I want to create a report where I can use a formula that lists the projects associated with a particular benefit. Example, I want Smartsheet to list if project x, project y and project z have the efficiency gain benefit under the benefits column.

Paul Newcome

Are you able to provide screenshots of the source data for reference/context?

Orlando Mercado

Paul Newcome

You would create a sheet that has each benefit listed out and then use a JOIN/COLLECT to pull in the various projects, etc. associated with that benefit.

Orlando Mercado

when using the Joint/collect formula, would it be something like the one below?

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}]:{MH-Work Prioritization Log Range 2}, {Benefits}:{Benefits}, "Efficiency Gain"), ",")

Paul Newcome

When using a cross sheet reference, you only need to enter the range once.

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}, {Benefits}, "Efficiency Gain"), ",")

Orlando Mercado

This is the formula I am inputting...

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}, {Benefits}, "Efficiency Gain),{Status}, "Prioritized Not Started","))

Paul Newcome

Now you have a misplaced parenthesis and missing quote at the end.

Started"), ",")

Orlando Mercado

Its still not working.

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}, {Benefits}, "Efficiency Gain), {Status}, "Prioritized Not Started"), ",")

Paul Newcome

Replace the closing parenthesis after Efficiency Gain with a quote.

Orlando Mercado

I get the error message Unparseable when I use the formula below.

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}, {Benefits}, "Efficiency Gain), {Status}, "Prioritized Not Started", ",")

Paul Newcome

The closing parenthesis after Efficiency Gain needs to be replaced with a quote.

Orlando Mercado

Its giving me an error or INVALID REFERENCE

=JOIN(COLLECT({MH-Work Prioritization Log Range 2}, {Benefits}, "Efficiency Gain", {Status}, "Prioritized Not Started", ","))

Paul Newcome

Make sure you are following the proper steps to create each of the cross sheet references.

Orlando Mercado

Paul Newcome

Not necessarily because you cannot group a report by a multi-select column.