Formula and functions

sanchezf

How can I keep this with the wording On track, Off track, at risk and assign a color to it like green, red, and yellow? Then pulled from the sheet to a dashboard to reflect those words and colors?

Paul Newcome

You would apply conditional formatting rules to the columns to highlight the cells based on the text. You would then use a metrics widget on the dashboard and select the option for "format painting" (looks like a paint roller) when setting up the widget to get the color/text combo to appear on the dashboard.

Danielle Arteaga

One way you might accomplish this is to add a "Symbol" column next to each of your current columns.

In each of those new symbol columns, you would place a column-level formula. For example:

=IF(Schedule@row="On track", "Green", IF(Schedule@row="Off track", "Red", "Yellow"))

Repeat this for each "status" column (changing the name of the [Column]@row reference as needed).

If you want an "overall" status for your project, you can use a nested IF statement, like so:

=IF(AND([Schedule Status]@row = "Green", [Cost Status]@row = "Green", [Scope Status]@row = "Green", [Quality Status]@row = "Green", [Resources Status]@row = "Green", [Risk Status]@row = "Green"), "Green", IF(AND([Schedule Status]@row = "Red", [Cost Status]@row = "Red", [Scope Status]@row = "Red", [Quality Status]@row = "Red", [Resources Status]@row = "Red", [Risk Status]@row = "Red"), "Red", "Yellow"))

If all your status columns are green, your Overall Status will be green. If they are all red, then your Overall Status will be red. Otherwise, the Overall status will be yellow.

Hope this helps!

P.S. Highly recommend making your text-based columns for On track, Off track and At risk Dropdown column types to ensure that typos or free-text entries do not break your status column formulas.

sanchezf

Thank you!