Conditional Formatting a cell with a formula that resolves to blank.

Jim Hook

I have a sheet with a column that is filled with formulas that brings in text data from another sheet. If the formula resolves to some text I want to highlight the cell with yellow. If the formula resolves to blank, meaning that there was no data to bring in from the other sheet (using JOIN(COLLECT)), I want to have no highlight. The conditional formatting seems to pick up the fact that there is a formula in the cell, and thus the cell is not truly blank, so even though the cell looks blank it gets highlighted. How can I only fill the cell with a color if there is actually something visible in the cell?

Andrée Starå

Hi @Jim Hook

I hope you're well and safe!

Strange!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots and the formula? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic 4th of July weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

Hi @Jim Hook 

Hope you are fine, you can add a helper column call it "Blank" and use the following formula:

=IF(ISBLANK([Join / Collect]@row), "", "1")

then design your conditional formating using the Blank column Value to highlight when the value is equal 1 as following screenshot:

Jim Hook

Hi Bassam,

Thanks for the suggestion and I agree it would work. I have used helper columns before for this type of thing but was trying to avoid it here since the sheet is already very large and a previous version of it ran into the 25 million cell reference limit. Since it appears that having a formula in a cell causes that cell to be non-blank regardless of the fact that nothing is visible, I may end up putting something like a "." in the cells I don't want to highlight so the conditional formatting can exclude them.

Jim

Bassam Khalil

@Jim Hook

An excellent solution to minimize the number of cell references ( Simplicity is the best ). i will be happy to help you any time.

Andrée Starå

@Jim Hook

I'm fairly confident that it shouldn't do what you're describing, and yu shouldn't need the helper column to get it to work.

Do you have a theory why it doesn't work as expected?

Jim Hook

Andree,

I ended up putting an inconspicuous "." in cells that didn't retrieve any text from another sheet and set the conditional formatting to not highlight cells that were equal to ".". That solved my problem.

My guess is that a cell that appears to be blank but has a formula in it is not blank from a conditional formatting point of view. I have many sheets that scan other sheets using INDEX(MATCH) formulas and if there is no data in the other sheet I set the cell to "". I guess this is the first time I've tried to conditionally format a cell with formulas that may not return any text.

Cmm Provan

Hi Jim,

I was having the same issue of conditional formatting not recognizing a "formula resulting in a blank" as a proper blank entry, so I ended up using the same 'period' solution you came up with and went looking for a better way and came across this post. It appears this is what to do for now until conditional formatting gets fixed in an update.