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

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?

Find more posts tagged with

Formulas

Support

Comments

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

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"))

Yoko Yabe

@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)?

progress bar.pdf

Genevieve P.

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

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.

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:

Screen Shot 2021-05-19 at 3.07.22 PM.png

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.

Screen Shot 2021-05-19 at 3.10.25 PM.png

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]#

Screen Shot 2021-05-19 at 3.17.40 PM.png

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:

Screen Shot 2021-05-19 at 3.20.03 PM.png

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:

Screen Shot 2021-05-19 at 3.22.07 PM.png

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

Yoko Yabe

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

Yoko

Sign in to join the conversation!

Quick Links

Trending discussions

Lookup Value Issues
I am trying to populate a column from Sheet A into Sheet B and Sheet C. My Lookup column for Sheet A is formatted as such so there is a ' in front to account for ones that have a leading 0 and some do not. Each ID should be 6 digits long. I made a helper column looking at this Dealer ID column to try and get rid of that…
Separating Data into Multiple Rows
am I able to separate this into multiple rows? in a new sheet? Desired Result -
Index Distinct Support
I would like to parse out this data by creating a listing with a row for each person, for each project they appear on, for each month. I think this would only give me the groupings anyways, but this doesnt seem to even get me started - it popped up then people have disappeared.