Conditional formatting for cells
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.
Comments
-
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.
-
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.
-
This content has been removed.
-
This content has been removed.
-
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.
-
This content has been removed.
-
Try this.
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.
-
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?
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.
-
This content has been removed.
-
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.
-
This content has been removed.
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!