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

How do I get a column that shows the automated % complete for each activity?
I am struggling to get any formulas to work, I need a column that shows the percentage complete from the start date to the finish date - shouldn't be that hard but this is causing me so much grief. Every formula I find doesn't seem to work and I am not savvy enough to know how to update the formula so that it can work.…
Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?