Conditional Format Blank Cells
Anyone have ideas on how to use one rule to conditionally format all blank cells in a row if "x" is true?
Best Answers
-
You would have to set up multiple rules as conditional formatting is applied at the column level.
-
It is in the hamburger menu (three horizontal lines) in the top left corner. Click on that and then on "Product Feedback".
In the meantime... You could insert a helper row (not a helper column) and then enter the column names into this row in each column. Then you can use a JOIN/COLLECT in a helper column to list out all columns that have a blank. Once you have the list on each row that shows which (if any) columns are blank, you can use a CTRL+F to search that helper row to jump to the appropriate column.
(Example formula below assumes "helper row" is on row 1)
=JOIN(COLLECT([First Column]$1:[Last Column]$1, [First Column]@row:[Last Column]@row, @cell = ""), "delimiter of choice")
Use conditional formatting based on this column to highlight which rows have a blank, or even apply a filter based on this column to only show rows that have data in it (meaning some other column in that row is blank).
Answers
-
You would have to set up multiple rules as conditional formatting is applied at the column level.
-
@Paul Newcome - thank you. Unfortunately there are over 50 columns, which is why I would like to highlight those that are blank. I'll send an enhancement request if/when I figure out where they moved it to.
-
It is in the hamburger menu (three horizontal lines) in the top left corner. Click on that and then on "Product Feedback".
In the meantime... You could insert a helper row (not a helper column) and then enter the column names into this row in each column. Then you can use a JOIN/COLLECT in a helper column to list out all columns that have a blank. Once you have the list on each row that shows which (if any) columns are blank, you can use a CTRL+F to search that helper row to jump to the appropriate column.
(Example formula below assumes "helper row" is on row 1)
=JOIN(COLLECT([First Column]$1:[Last Column]$1, [First Column]@row:[Last Column]@row, @cell = ""), "delimiter of choice")
Use conditional formatting based on this column to highlight which rows have a blank, or even apply a filter based on this column to only show rows that have data in it (meaning some other column in that row is blank).
-
@Paul Newcome - very helpful, thank you!!
-
Happy to help. 👍️
-
Paul -- could you provide screenshots of your example? I'm trying to put this in place using the helper row & column and having issues with the Join(Collect) formula
-
@Ashley Heath You would insert a new row at the top of the sheet. In this row you would manually enter each of the column names. You would then use the above formula to pull in the list of columns that have a blank cell on that row.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives