Automatically update current stage

Hi,


I am trying to track progress through various stages of publication (the columns titled approval, data collection, analysis...accepted). I thought the health bubbles may be a good way to quickly visualize progress but it is confusing the card view as it can only organize per specified column at a time. What would be the best way to format so that I'm able to generate a report of the current stage (designated by a yellow or red bubble) of any project (presented in rows)?

I thought adding a "current stage" column would help, though, I would want the "current stage" column to automatically populate the name of the column(s) that have a yellow or red bubble.

Feedback welcome!

Esperanza

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The best way I can think of to automate it would be to create a helper ROW across the top where you manually enter each of the column names for the different stage columns.


    Then you could use something along the lines of...

    =INDEX(Approval$1:Submitted$1, 1, COUNTIFS(Approval@row:Submitted@row, "Green") + 1)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you Paul, I feel like that's on track. Would the helper row be above the first row, which the screenshot shows as "Main outcome"?

    and if I'm interested in stages approval to accepted would the code read

    =INDEX(Approval$1:Accepted$1, 1, COUNTIFS(APPROVAL@row:Submitted@row, "Green")+1)

    sorry if this sounds like a silly question, but I don't know what $1 indicates?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You are correct in the first two.


    The last one... $ locks in the reference. If it is before the column name, it will lock in that column name. If it is before the row number then it locks in the row number. So as you dragfill or as auto-fill pulls the formula into new rows, it will always reference row 1.

    This means that the INDEX function will always pull data from the first row based on the data in the current row (@row).

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you Paul for your input.

    I'm struggling to understand the formula as written. If I am interested only in yellow or red circles do I change "Green" to "Yellow or Red"?

    Ultimately, I'd like some thing that populates the column title (Approval:Accepted) of each yellow or red bubble per row. For example, for the pictured row 1, it would read "Final" as the current stage, "Analysis" and"Outline" for the 2nd row, etc.


    Thank you!

    Esperanza

  • I was thinking of something like this in the Current Stage column:

    =IF(Approval@row = "Yellow", "working on Approval"), IF(Data collection@row="Yellow", "working on Data Collection")

    ...and so on to "Accepted"

    What I have doesn't work though. Trying to figure out why.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    When using a column name that has spaces, special characters, or numbers in it, you need to wrap the column name in square brackets.


    =IF(Approval@row = "Yellow", "working on Approval"), IF([Data collection]@row="Yellow", "working on Data Collection")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • I'm getting a #UNPARSEABLE error message.



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 07/27/20

    Sorry. I missed that you are also closing out each of your IF statements individually. You should string them all together and then close them all at the end.


    =IF(Approval@row = "Yellow", "working on Approval", IF([Data collection]@row="Yellow", "working on Data Collection", IF(........................................)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thank you Paul.


    I'm fortunately I'm getting the error message with and without the paranthesis bringing the IF statement at the end. To complicate matters, I'd also like to add to the statement that the Current Stage should read "working on [column]" if the circle is yellow or red. Would this be possible?


    Thank you, and I'm sorry for the challenge!


    Esperanza

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Can you copy/paste the exact formula you are using directly from the sheet?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Ok, think I got it to work after reviewing the parentheses (equation below).

    Some rows are working on multiple yellow bubbles (working on multiple stages at once), I would like the current stage column to reflect that. For example saying, "working on Analysis", "working on Outline", etc. Is that posible?

    Also I would like Yellow and Red bubbles to generate a "working on..." command.

    =IF(Approval@row = "Yellow", "working on Approval", IF([Data collection]@row = "Yellow", "working on Data collection", IF(Analysis@row = "Yellow", "working on Analysis", IF(Outline@row = "Yellow", "working on Outline", IF(Draft@row = "Yellow", "working on Draft", IF(Final@row = "Yellow", "working on Final", IF(Submitted@row = "Yellow", "Submitting", IF(Resubmission@row = "Yellow", "working on Resubmission", IF(Accepted@row = "Yellow", "waiting for notice of Acceptance")))))))))

    Thank you!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Yes. You could separate your IF statements and "add" them together into a string.

    =IF(Approval@row = "Yellow", "working on Approval, ") + IF([Data collection]@row = "Yellow", "working on Data collection, ") + IF(...................................


    Or you to have an output of

    "Working On: Analysis, Outline, ..............(continue listing out yellow/red columns for the row)"

    ="Working On: " + IF(Approval@row = "Yellow", "Approval, ") + IF([Data collection]@row = "Yellow", "Data collection, ") + ...................................


    And yet another option (which I feel provides the cleanest output) would be to leverage the helper row outlined above and then use a JOIN/COLLECT to pull those that are either yellow or red.

    ="Working On: " + JOIN(COLLECT([First Color Column]$1:[Last Color Column]$1, [First Color Column]@row:[Last Color Column]@row, OR(@cell = "Red", @cell = "Yellow")), ", ")

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!