Can you apply conditional formatting to a range of cells based on the value within each cell?
I have a table of employees and am trying to create a visual display of their attendance for a given month. The cells populate automatically from another sheet using a the index function and will display one of the following letter codes: W, A, S, O, X, C, H
I was able to achieve the desired background formatting for a single column having created 7 rules. To account for the situational variance in calendaring, I have a total of 42 columns that would need to have these same 7 rules. Cloning each is helpful but not 42 degrees worth of helpful.
Hoping someone can provide a fresh perspective and some insight on how to configure this in a less manually intensive manner.
Best Answer
-
Hi @Jim Rood
With Smartsheet's current Conditional Formatting functionality, no, there isn't a way to set a criteria to look through the entire sheet and apply formatting. You would need to create 7 rules per-column.
Please let our Product team know about your use-case and request by filling in this feedback form, here!
Would it be possible to re-structure your attendance sheet? If you posted a screen capture of the sheet (blocking out sensitive data) I'd be happy to look and see if there's an alternate way of displaying this information, or a formula that could help with formatting.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Jim Rood
With Smartsheet's current Conditional Formatting functionality, no, there isn't a way to set a criteria to look through the entire sheet and apply formatting. You would need to create 7 rules per-column.
Please let our Product team know about your use-case and request by filling in this feedback form, here!
Would it be possible to re-structure your attendance sheet? If you posted a screen capture of the sheet (blocking out sensitive data) I'd be happy to look and see if there's an alternate way of displaying this information, or a formula that could help with formatting.
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you for confirming my suspicions. I submitted an enhancement request as you suggested. Here is that picture. The conditional formatting consisting of the 7 rules took care of column Mon (1) but left the daunting task of "cloning" it for the others.
I think I found an acceptable work around using emojis. The "headers" in the summary columns to the right are simple colored emojis that I plan on using in lieu of the letter codes. They work nicely in the single select drop downs where I can include characters. Then when selected in the collector sheet I can pull in only the first character giving me the visual I would have liked to have created with the conditional formatting.
-
Hi @Jim Rood
Yes I can see what you mean; I think using emojis is a good idea, and likely the best way to get the colouring you're looking for.
Thank you for posting this workaround!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Upon playing with this a little further, @Genevieve P , I did encounter an unanticipated glitch.
🟩 Work
This is the value that displays in my drop down list and populates perfectly in the cell when selected. When I attempt to only retrieve the first character via Left([Attendance Status]@row, 1) it didn't quite return the results I had hoped: �
As I was just about to abandon hope, something prompted me to revise the formula to pull in more than one character. Changing it to two characters worked like a charm. 😀
Who knew emojis could be so useful!
-
That's very interesting!! I'm glad you didn't give up... I wouldn't have expected the emoji to count as more than one character, and I'm really glad I know this now.
Thanks!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives