Ignore Formula entry but not other entries

**DHFE** · 03-17-2014, 06:48 AM

I have a cell with a formula in it. I want to use conditional formatting on this cell if the formula result leaves it blank. Given the formula is in the cell it's never recognised as blank.
If an entry is input instead the formula then obviously I don't want the conditional formatting.

What formula can I use in the conditional formatting for it to recognise the Cell formula as blank?

**adyteo** · 03-17-2014, 06:56 AM

Use a helper column (let's say your formula is in A2, use B2 as the helper). in B2 you write =A2
then, A2 conditional formatting, "use formula to determine which cells to format" and you point to B2 if empty. does it work?

**Richard Buttrey** · 03-17-2014, 06:56 AM

Use exactly the same formula as the cell itself contains but with an additional True/False evaluation.

i.e.

=yourformula=""

**DHFE** · 03-17-2014, 07:07 AM

Sorry, i don't think i'm understanding.

Cell A1 is blank
Cell B1 has a formula

If Cell A1 contains an entry i want Cell B1 to have conditional formatting - but only if B1 is blank. The formula in B1 will only NOT be blank 1/10 times.
However because Cell B1 contains a formula it is not blank - therefore the conditional formatting doesn't ever work.

**Richard Buttrey** · 03-17-2014, 12:46 PM

Hi,

That isn't what your original post said. You only mentioned one cell. Now you are telling us that two cells are involved. We are not psychic (although I'm getting better at it) and you really do need to mention all salient points. Giving a specific example which names cells that are involved is always a good starter.

So for B1 apply the CF

Formula:

Please Login or Register  to view this content.

**DHFE** · 03-18-2014, 04:27 AM

The other cell has no bearing on what i want other than it activates the conditional formatting. I've managed to solve the issue myself.
Your formula =and(a1="",b1="") doesn't work because as i stated in my first post - any conditional formatting formula doesn't recognise cell B1 as blank because it has a formula in it...

**Richard Buttrey** · 03-18-2014, 08:46 PM

Hi,

Seems to work OK for me. See attached.

Observe that B1 contains a formula:

Formula:

Please Login or Register  to view this content.

With A1 blank and C1 anything but 1 then B1 is painted red by the CF
Any entry in A1 removes the CF as does any value <>1 in C1

That seems to satisfy your laid down criteria and requirement. Perhaps you have some other logic in mind.