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.
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)
You need to have <> "" after each cell reference.
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.
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.
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.
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.
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)
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.
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".
YAAAAAAY! Success! Sorry I was overthinking. I so appreciate your help Paul!
Happy to help. 👍️
I'm wondering if there is a way to lock rows 1-12 out of the sorting and filtering of a sheet. I might have take out that part of the from otherwise I dont like how the built in dashboard moves when a sort or filter is applied?
Hello, I apologize if this has already been answered, I did search before asking. I recently downloaded a template and it had the google slides displayed in presentation mode. I cannot figure out how to do this as when I embed, it's just the entire google page where you can edit and see the menu bar. How do I embed the…
I'm working on a compensation request form that follows these steps: An HR Business Partner completes the Compensation Request Smartsheet Form that then triggers an alert to our Compensation Manager. Our Compensation Manager will insert the requested information. The HR Business Partner will be alerted when the…
Help shape the future of Smartsheet.
Share your ideas and feature requests.
©2023. All Rights Reserved Smartsheet Inc.