Collect multiple project names in Portfolio Rollup?

Data is collected in this example intake sheet below and the metrics are summarized in another metrics sheet below.

The following formula collects the Project ID and gives the count per associated Platform and Status. However, I need to display the Project Name as well as the count on my portfolio dashboard.

How can I fix the Join formula to collect the various project names? (The IF statement is for removing counts of zero to hide them from the scatterplot).

=COUNT(COLLECT({Project ID}, {Project Status}, $Label@row))
=IF(COUNT(COLLECT({Project ID}, {Project Platform}, [Platform 1]$1, {Project Status}, $Label@row)) >= 1, COUNT(COLLECT({Project ID}, {Project Platform}, [Platform 1]$1, {Project Status}, $Label@row)), "")
=JOIN((COLLECT({Project Name}, {Rollup Status}, $Label@row)), ", ")

Here is the output I am currently getting:

Here is an example dashboard, where I would like to display the project name in each platform summary.

Find more posts tagged with

Formulas

Dashboards

Project Management

Accepted answers

Genevieve P.

Hiya! Thanks for the tag, @Lucas Rayala

That is strange. @breso would you mind trying a few things for me?

Could you put a filter on the source sheet with the same information to see what those 13 rows are?
Can you double check the {Project Name} reference to make sure it's not looking at a different column, that somehow only has "Project 1" in a cell? (Or maybe a duplicate sheet?) It would be helpful to see a screen capture of the reference window open, showing the column highlighted.
What happens if you add in a DISTINCT function:

=JOIN(DISTINCT(COLLECT({Project Name}, {Rollup Status}, Label@row)), ", ")

And final question... how is the Project Name column being populated in the source sheet, is this a manual entry?

Thanks!

Genevieve

All comments

Lucas Rayala

@breso, your join is mostly right, however you don't need the "$". I also removed a redundant set of parenthesis around the collect function -- can you try this?

=JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row), ", ")

breso

Hi @Lucas Rayala,

Thank you for the response! I copied the formula but am still get the same output "Project 1, , , , , , , , , , , ,".

Lucas Rayala

Hi @breso this seems to imply it's finding a lot of empty cells in the Project Name where the Rollup Status is "Active". At first glance that doesn't appear to be the case in your file, but add an exclusion just to be safe:

 =JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row, {Rollup Status}, LEN(@cell)>0), ", ")

(I like to use the LEN function--which find the number of characters in a cell--to evaluate null cells, but you can do @cell<>"" as well)

You can clean the hanging comma after you get this figured out.

breso

@Lucas Rayala,

Thanks again! It still has the same output though Project 1, , , , , , , , , , , , 😑. Is there an array function in Smartsheet maybe the names are getting replaced by blank spaces? Because the count version of this returns 13 with matches the commas. It should be grabbing all the project names in the table above except for those that are On-Hold (Rollup Status Column). But at this point it's okay if there's not really a way to do this because I doubt that I can display all those names in a Dashboard chart.

Lucas Rayala

@breso that is odd. try replacing "Label@row" with the actual word "Active" to see what you get. @Genevieve P. , have you run into this? quick summary, he's got a simple JOIN -- COLLECT function that should pull content from [Project Name] based on [Rollup Status].

The statement is this:

  =JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row), ", ")

Where "Label@row" corresponds to the rollup status. When he points Label@row to the word "Active", he's getting the first "Project 1", but then a series of commas, which doesn't grok. Ideas?

This is what he sees:

"Project 1, , , , , , , , , , , ,"

Genevieve P.

Hiya! Thanks for the tag, @Lucas Rayala

That is strange. @breso would you mind trying a few things for me?

Could you put a filter on the source sheet with the same information to see what those 13 rows are?
Can you double check the {Project Name} reference to make sure it's not looking at a different column, that somehow only has "Project 1" in a cell? (Or maybe a duplicate sheet?) It would be helpful to see a screen capture of the reference window open, showing the column highlighted.
What happens if you add in a DISTINCT function:

=JOIN(DISTINCT(COLLECT({Project Name}, {Rollup Status}, Label@row)), ", ")

And final question... how is the Project Name column being populated in the source sheet, is this a manual entry?

Thanks!

Genevieve

breso

Hello @Genevieve P. & @Lucas Rayala,

Thanks for all the troubleshooting ideas, your solution worked!

(Image 1) I applied the filter on the source sheet (Project Intake Sheet).
(Image 2) I verified the references and Rollup Status had the correct Source, but Project Name was linked to the Project Metadata Sheet for Project 1 instead of the Project Intake Sheet. The issue was corrected!! It makes sense that it couldn't find the Project Names.
Also, I tested with the DISTINCT function before I corrected the Project Name source and it just removes all the commas " ," .
(Image 3 + 4) Although, I now realize now that I won't be able to display the names in a scatter chart and make the Project Pipeline/Funnel/Bubble chart that I was going for.

Lucas Rayala

Hi @breso glad it worked! I knew @Genevieve P. would know what to do.

If you want something that would work visually, you could updated the formula to:

=JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row), CHAR(10))

The "CHAR(10)" is a carriage return, which would put everything as a vertical list in the one cell. If you hover over that cell, the entire list would pop up in hover text. However, the first item would show and would likely confuse people, who might think that's the only item.

You could do one of two thing to correct this:

Wrap your text: this would expand the cell height to the height of the list. Which may well be undesirable.
Change the formula to something like this:

=IF(COUNT(COLLECT({Project Name}, {Rollup Status}, Label@row))>1, "Hover for list" + CHAR(10) + JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row), JOIN(COLLECT({Project Name}, {Rollup Status}, Label@row))

What this would do: if the return list is more than 1 item, it would add the phrase "Hover for list" as the first item of the return. That would key people to actually hover over the cell to trigger the popup and see the rest of the contents. Just a thought! Glad you were able to at least figure it out :)

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.