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

  • Genevieve P.
    Genevieve P. Employee
    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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

Answers

  • Genevieve P.
    Genevieve P. Employee
    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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Jim Rood
    Jim Rood ✭✭✭✭

    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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.

  • Jim Rood
    Jim Rood ✭✭✭✭

    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!

  • @Jim Rood

    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 for Jumpstart 2025 with Community on 23 January (in two time zones)! 🎉 Register here.