Conditional formatting for cells

jb@59069

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.

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.

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

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.

Andrée Starå

Try this. 

https://cl.ly/b9f606

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.

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.

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