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

Jim Rood

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.

Genevieve P.

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

Genevieve P.

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

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.

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

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.

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