Can you apply conditional formatting to a range of cells based on the value within each cell?

Options

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 Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • Jim Rood
    Jim Rood ✭✭✭✭
    Options

    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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!

  • Jim Rood
    Jim Rood ✭✭✭✭
    Options

    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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    @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!