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

Options
Jim Hook
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?

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    Options

    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
    Bassam Khalil ✭✭✭✭✭✭
    Options

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jim Hook
    Jim Hook ✭✭✭✭✭✭
    Options

    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
    Options

    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.