Conditional formatting for cells

jb@59069
jb@59069 ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

I have a Retainer Budget Summary that I need some help with conditional formatting, please. I have attached a snip of the summary.

How can I set conditional formatting to change colors of values in individual cells when:

1) Billed Hours reaches 75%, 85%, 95% of Budgeted Hours;

2) Hours Remaining exceeds Budgeted Hours (value is < 0)

It seems that since conditional formatting only works on the column, that formatting for rows or cells is not available?

Or what am I missing?

Is there a way to have an RYG ball or flag column(s) that would trigger based on formula(s) keyed on the comparative values or negative value?

Can a notification/alert be triggered on any one of the above?

I've been racking my brain and pouring through all the videos and community searches I can think of.

Thank you.

RetainerSummary_15Oct2018.JPG

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Set your conditional formatting for a single column that will highlight only the rows in that column depending on your criteria. You can then clone the rule and adjust which column it is for.

     

    Another option would be to flip your layout. Have the dates going down the first column and your summary fields as your columns. That would make setting up your alerts and notifications easier as well.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    This might help.

    Please see the attached link/screenshot for more information.

    https://community.smartsheet.com/discussion/conditional-formatting-cell-level

    I hope this helps you!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. Yeah. Seeing the size of your sheet definitely makes me say disregard flipping it.

     

    To set up conditional formatting on a "cell" level, you would actually set it up per column. Then repeat the rule for each column. It's a semi-tedious endeavor, but it will give you your cell specific results.

     

    In the image below, I wanted to highlight any cell that was less than 0%. You will see in my conditional formatting rules I specified that if Percentage 1 was less than 0, then highlight only the Percentage 1 column. Same for 2 and same for 3.

     

    Obviously you'll adjust the conditions/column names/etc to fit your particular needs, but this would be the general idea.

    Capture.PNG

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Try this. 

    https://cl.ly/b9f606

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Andree

    I can't open your link (work computer), so sorry if I'm just repeating what you have linked. If your suggestion is less tedious than mine, would you mind giving a quick rundown of it?

     

    @jb

    You'll have to create a new rule for each set of criteria. When setting the rule, select the option to define custom criteria, and you can select "is between" from the dropdown. Use decimals for the percentages (50% = .50, 100% = 1).

     

    You'll then duplicate and tweak each of those rules for each column.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. My mistake. I misread what it is you wanted. You are going to need to add a row that calculates the actual percentages and build your conditions from there. Either that or manually enter your number ranges after calculating the percentages yourself. Conditional formatting doesn't have an option for when one cell is a certain percentage of another. You have to have that percentage calculated already and build the rule off of that.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Glad I could help. And no worries about not being clear upfront. Sometimes it is tough to convey EXACTLY what you are looking for. Then add human assumptions and difference of opinion/difference in view... Sometimes it can take a while. Haha

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!