Change status if progress bar changes (help w/syntax)

V1LL4M4R
V1LL4M4R
edited 12/09/19 in Formulas and Functions

Hello I want the status to change if the progress bar is changed. I tried a formula but only have it half working. 



=IF(AND(Progress1 > "Empty", Progress1 < "Full"), "In Progress", IF(Progress1 = "Full", "COMPLETED!")



This is the formula. I want the status to show either 'Not Started' , 'In Progress', or COMPLETED! based on the progress of status bar. I only have the last part full= completed bit working... any help?



 

Capture.PNG

Comments

  • Connor Hartford
    Connor Hartford ✭✭✭✭✭

    Hi V1LL4M4R,

    I don't believe you can perform 'greater than' or 'less than' logical expressions on symbols.

    If I understand what you are after correctly, this is the formula you'll want to put in your status column:

    =IF(Progress@row = "Empty", "Not Started", IF(Progress@row = "Quarter", "In Progress", IF(Progress@row = "Half", "In Progress", IF(Progress@row = "Three Quarter", "In Progress", IF(Progress@row = "Full", "Completed", "")))))

    Connor


    Connor Hartford

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You could also just specify the criteria for "not started" and "completed" since they only have 1 option each and then use the 3rd portion of the IF statement to tell it to populate in progress if it is not "completed" or not "not started".

     

    =IF(Progress@row = "Empty", "Not Started", IF(Progress@row = "Full", "COMPLETED!", "In Progress"))

  • @Paul Newcome I have been trying to figure out how to set up an automated progress bar in a dashboard. I came cross your comments several times and you seem to know a lot about progress bar. I would like to create progress bars like this. See attached. Is there a way to make this type of progress bar (hopefully automated)?


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Yoko Yabe

    Dashboard data needs to be sourced from somewhere else. For example, you could have a Chart with a Bar where the Bar graph changes depending on the underlying data, or a Pie Chart with percents (see: Using Chart Widgets) or you could display data from an underlying sheet in Metric Widgets (see: Using Metric Widgets).

    This Webinar may be useful for you: SmartStart: Dashboards

    The status bars discussed in this thread are about the Symbol column in a Sheet.

    Do you have a sheet with data where you want to track the status of each row? Or are you looking to track an overall percent of details in a sheet? It would be helpful to see a screen capture of the source sheet where you are tracking data, with a description of what you're looking to track, but please block out any sensitive data.

    Cheers,

    Genevieve

  • Yoko Yabe
    Yoko Yabe ✭✭
    edited 05/18/21

    Thank you, @Genevieve P . Let me share what my sheet looks like and what I want to accomplish.

    😋


    Let me know if this makes sense. If you could guide me how to make a stacked progress bar, that would be very helpful!

    Best regards,

    Yoko

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Yoko Yabe

    Thank you for clarifying! There currently isn't a Progress bar chart like this available to create in Smartsheet Dashboards. What I would personally do is create formulas in the underlying sheets to then display a combination of Metric widgets (displaying the status for each Parent Task), and a Chart Widget (displaying the overall percent complete, or your "Marker" on the left).

    My end result looks like this:


    Here is how I created this Dashboard.


    Step 1: Helper Column with Parent Row Names

    I inserted a column in my sheet to identify the Parent row names for each Child task. The formula I used (and turned into a Column Formula) in simply

    =PARENT([Study Name]@row)

    You can hide this column in the sheet after you've set all this up, if needed.


    Step 2: Create Status Ball Metrics in Sheet Summary for each Parent Row

    I then created a Sheet Summary field for each of the Parent Rows. In these fields I used a formula to identify if the Parent Row is currently Green, Yellow, Red, or Gray.


    The formula I used is as follows:

    =IF(COUNTIF(Parent:Parent, "Sample Management") = COUNTIFS(Parent:Parent, "Sample Management", Status:Status, "Completed"), "Green", IF(COUNTIFS(Parent:Parent, "Sample Management", Status:Status, "On Hold") > 0, "Red", IF(COUNTIFS(Parent:Parent, "Sample Management", Status:Status, "In Progress") > 0, "Yellow", "Gray")))


    This says, if the number of rows below "Sample Management" is the same number that says "Completed", then the status is Green. Otherwise, if there is even ONE task that says "On Hold", the status is Red. Otherwise, if there are no tasks that say "On Hold", but there's ONE task that says "In Progress", it's yellow. Otherwise, it's Gray (because it's Not Started or Blank).

    You would just need to swap out the Parent name "in these" to search for each of the different top level rows.


    Step 3: Identify the % Complete

    Now that you have each parent row with a status, you can identify that when a certain parent is "Green", that equals a specific % Complete:

    =IF([Sample Management]# = "Green", 0.2, IF(Section# = "Green", 0.4, IF(Starin# = "Green", 0.6, IF(Scan# = "Green", 0.8, IF([Image Analysis]# = "Green", 1, 0)))))


    Step 4: Identify the remaining %

    To find the remaining % to be completed, simply minus the previous formula from 1

    =1 - [Marker Percent]#



    Step 5: Create a Summary Report for the % Fields

    Create a Sheet Summary type of Report, selecting just these two fields. This will allow you to select the Report as the Source for your Chart Widget:


    Step 6: Create your Dashboard

    The rest of the fields can be added as Metric Widgets, selecting the Sheet Summary section as the source for your data:


    Now the data will automatically adjust and reflect current status colours in your Dashboard, based on the underlying sheet! Will this work for you? Let me know if you're having trouble with any of the steps above and I'm happy to help further.

    Cheers,

    Genevieve

  • @Genevieve P Thank you so much for the information and instructions. I will review and try out! Really appreciated!

    Yoko

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!