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.
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!
-
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.
-
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.
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!
-
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.
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!
-
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!
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!