Can I create conditional formatting to highlight an entire row of columns?

Tjmarget

Hello,

I have a sheet set up to keep track of Overtime, Comp Time and Sick Leave requests. There are 14 columns. I want the row to change color when ALL 14 are filled in. Is this possible?

I'd appreciate any help.

Terri

Tjmarget

Hi Paul,

If there are drop down choices, I put @row<>"" and if it is a cell that is either blank or populates like names or dates, I clicked on it. Below is how it currently looks and I am getting an "Invalid Data type" error. Any ideas? Much appreciated!

=IF(AND([Employee Name]1, [T&L]1, [OT/CT/ Sick Leave]@row <> "", [Start Date]1, [Start Time]1, [End Date]1, [End Time]1, [Total Time]@row <> "", Justification1, [Scheduled?]@row <> "", [Call Back?]@row <> "", [Lunch Break?]@row <> "", [Supervisor Approval]@row <> "", [VATAS Status - Timekeeper Only]@row <> ""), 1)

Paul Newcome

You need to have <> "" after each cell reference.

Paul Newcome

The easiest way to set this up would be to create a helper column that would flag when the row is filled out completely and then build your conditional formatting off of the helper column.

Tjmarget

Thank you Paul! I am new to Smartsheet. Can you provide a link with instructions on how to create a helper column? I did a search a nothing is coming up.

Paul Newcome

A helper column is simply an extra column that you add. It can be any column type depending on your needs. Helper columns don't always contain data that is pertinent to the sheet but helps make things happen automatically. I generally hide my helper columns to keep from cluttering up the sheet too much.

For this particular situation, I personally would use a checkbox type and then use a formula to automatically check the box once the requirements are met.

Tjmarget

Follow up question. Most of my columns have many options to choose from. How do I display them all in a formula so that when ANY option is chosen (in each column), the formula works? Hopefully that makes sense. I have been trying to upload a picture but for some reason it isn't working for me.

Paul Newcome

Instead of looking for specific options, you can look for "non-blanks" by using the criteria of

[Column Name]@row <> ""

=IF(AND([1st Column]@row <> "", [2nd Column]@row <> "", [3rd Column]@row <> "", ........................................, [14th Column]@row <> ""), 1)

Tjmarget

Do I put a range in between the <> or ""? Sorry Smartsheet formulas are still confusing to me ... I appreciate it. I can share my sheet with you so you can see what I'm looking at if you'd like. 

Paul Newcome

No. The <> "" portion stays as is. The only part you need to change is making sure the column names are correct and all there.

<> is the same as "not equal to" and double quotes are the same as saying blank. So <> "" is basically saying "Not Blank".

Tjmarget

Hi Paul,

If there are drop down choices, I put @row<>"" and if it is a cell that is either blank or populates like names or dates, I clicked on it. Below is how it currently looks and I am getting an "Invalid Data type" error. Any ideas? Much appreciated!

=IF(AND([Employee Name]1, [T&L]1, [OT/CT/ Sick Leave]@row <> "", [Start Date]1, [Start Time]1, [End Date]1, [End Time]1, [Total Time]@row <> "", Justification1, [Scheduled?]@row <> "", [Call Back?]@row <> "", [Lunch Break?]@row <> "", [Supervisor Approval]@row <> "", [VATAS Status - Timekeeper Only]@row <> ""), 1)

Paul Newcome

You need to have <> "" after each cell reference.

Tjmarget

YAAAAAAY! Success! Sorry I was overthinking. I so appreciate your help Paul!

Paul Newcome

Happy to help. 👍️