Help with Conditional Formatting

Hi! I'm trying to highlight cells for 3, 6, and 12-month averages based on a 10% or 5% increase or decrease relative to a target, depending on the title. However, I've noticed that Smartsheet's conditional formatting prioritizes higher rules over lower ones, which is affecting my setup.

Is there another way to do this?

Thank you!

Find more posts tagged with

Accepted answers

All comments

kowal

hi @aneves,

yes that's correct whatever is higher position on conditional formatiting list the higher priority it will get.

You need to follow the logic as an example:

Once I created the sheet with conditional formatting that if the End Date is in more than 2 (is in 1000 days) weeks from now in the future the row is green. If the End Date is withthin next two weeks it shall be orange (is in 14 days). If the End Date is smaller than 1 week (is in 7 days) from now it shall be RED.

To make it work for me I needed to put the Green on the bottom, orange in the middle and red in the top of my conditional formatting otherwise if Green is on the top everything will be always green.

Hope this help you understand the logic :)

aneves

Hi! I was looking for a formula that can assist turning the symbol to a specific color based on other criteria. Any thoughts on how to do that?

=IF([Column1]@row > 0.50, "red", Name@row, ="XXX")

=IF([Column1]@row > 0.70, "yellow", Name@row, ="XXX")

=IF([Column1]@row > 0.90, "green", Name@row, ="XXX")

kowal

hi @aneves ,

you will need to follow the proper structure of "if" formula:

=if(condition, what happens if condition met, what happens if condition dees not meet)

so it should be more like: =if(column!@row>0.50, "Red", "Yellow") etc.

as you want to meet two conditions you need to also add formula AND so it will look like:

=if(AND([Column1]@row > 0.50, Name@row, ="XXX"), "Red", "sth that happens if those two conditions are not meet) etc.

hope it helps.

aneves

Hi again @kowal,

I tried =IF(AND([Column1]@row > 0.585, Title@row, ="XXXX"), "Red") and didn't work. Any insights why it didn't work?

Thank you!!

kowal

hi @aneves,

the function is very simple so also very simple to test.

Did you get error when you tried to use it or the the column did not turn red when you met those both conditions?

aneves

Hi @kowal -

It's saying #Invalid Data Type. Here is the formula:

=IF(AND([AAAA]@row > 0.81, BBBB@row, ="XXXX"), "Red")

Am I missing anything?

Paul Newcome

@aneves How exactly is [AAAA] populated? Are you able to provide some screenshots for context?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.