Weekly Status Bubble from individual projects carried to Portfolio view automatically

Hey there - and thank you in advance for any assistance. 😀

We have PMs in their projects assign a green/yellow/red bubble status indicator weekly. The sheet has row entries based on the week where they choose the bubble and other data elements.

Today, on a separate portfolio summary sheet used for the portfolio dashboard, they also manually update the bubble indicator. I want the portfolio sheet to pull the "latest" date bubble status indicator and display it (to remove the second manual update). A link doesn't work as a new entry is added to the individual project status report weekly.

I've been unsuccessfully scratching my head trying to figure out how to instruct the portfolio sheet to display the latest entry bubble from the individual project status sheet. Help - Is this even possible?

Find more posts tagged with

status

Accepted answers

KPH

Perfect!

You can use the date column to find the most recent status. The formula will be:

=INDEX(COLLECT({status column},{date column},MAX({date column})),1)

Where the bits within the { and } are the names you give to your cross sheet references (let us know if you need help setting up cross sheet references).

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

This means return the value in the status column where the value in the date column is the largest value in the date column.

All comments

KPH

Hi @Sharee Brell

Short answer is "probably". The long answer will depend on what you have in the sheet the PMs edit that defines "latest bubble".

You could do something like INDEX the column that has the bubble in, and use MATCH or COLLECT to return the correct row based on something in the sheet the PMs edit. It could be the highest row number, the row with the most recent date that also has a bubble, the most recently updated row (based on the timestamp Smartsheet adds when rows are edited), the most recently created row …

How do you know which is the latest row?

Sharee Brell

@KPH - I have a date column with which the update is associated.

KPH

Perfect!

You can use the date column to find the most recent status. The formula will be:

=INDEX(COLLECT({status column},{date column},MAX({date column})),1)

Where the bits within the { and } are the names you give to your cross sheet references (let us know if you need help setting up cross sheet references).

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references

This means return the value in the status column where the value in the date column is the largest value in the date column.

Sharee Brell

Awesome - thank you - will try this out!! I REALLY appreciate the assistance!

KPH

No problem at all. Glad I could help.

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.