Conditional formatting to treat formula as blank

[anglarna_ger]

Hi

I've got conditional formatting set up to do nothing if another cell is blank, or change colours based on the value of the same other cell.

The problem I'm having is the other cell contains a formula which only shows a result if certain conditions apply.

Because there's a formula in the other cell, the conditional formatting won't treat the cell as blank - even when the formula returns a blank.

I had thought of formatting the cell to stay as it is if it contains the formula, but there are over 100 different cells with different versions of the formula, so it would take forever to set up.

Is there anything that can be done?

Thanks

[leelnich]

Hi @anglarna_ger - You need to test if value ="", not ISBLANK() . If you post your formula ,we can be more specific.
We'll need to know what kind of data is contained/returned in any relevant cells -TEXT, NUMBERS, or "".

[anglarna_ger]

apologies for the late reply

the cell range that the conditional formatting reads from (not the cell being formatted) contain a variation of the formula

=IFERROR(INDEX(Results!$A$2:$A$33,SMALL(IF(Results!B$2:B$34="WON",ROW(Results!$A$1:$A$33),""),ROWS($A$1:$A1))),"")

The formatting is based on

If formatted cell is blank -- no formatting
If cell range being read from is blank -- no formatting
if formatted cell contains text which is contained within any of the cells in the range being read from -- format green
If formatted cell contains text which doesn't appear within cell range being read from -- format red

Because there's a formula in each of the cells within the range, they aren't treated as blank even when they aren't returning a result.

Is there a way to make the conditional formatting ignore the formula and read the result?

[leelnich]

It's easier if we have a workbook to look at, but... This example applies 2 conditional formatting rules to column A, based on same-row comparisons.

CF rule#1 (GREEN):
=IF(A2 <> "",COUNTIF($B2:$D2,A2) > 0)

CF rule#2 (RED):
=IF(A2 <> "",AND(COUNTIF($B2:$D2,"") < COUNTA($B2:$D2),COUNTIF($B2:$D2,A2)=0))

Row\Col	A	B	C	D
1	Cond. Formats	_____	Check	Cells
2		a	b	c
3	a
4	a	a	b	c
5	d	a	b	c

[anglarna_ger]

sample attached.

Needs to be no formatting if there are no results for that week, green if someone has picked a winner that week and red if they haven't.

Thanks

[leelnich]

OK, just like post#4, except you're doing same-COLUMN comparisons instead of same-ROW. Select C2 and paste the following CF formulae:
Winners (GREEN):

=IF(C2 <> "",COUNTIF(C$35:C$51,C2) > 0)

Losers (RED):

=IF(C2 <> "",AND(COUNTIF(C$35:C$51,"") < COUNTA(C$35:C$51),COUNTIF(C$35:C$51,C2)=0))

The RED formula should be simplified if the upper (formatted) and lower (WINNERS) sections are always filled in at the same time:

=IF(C2 <> "",COUNTIF(C$35:C$51,C2)=0)

[anglarna_ger]

perfect thanks