Conditional formatting (by Formula) - few questions

[Pete]

Hello!

I'm currently working on some documents where I want to apply background color depending by a variety of conditions.

One example:
fill a whole row from e.g. A1:G100 where the cells C to G are empty
I managed to find a formula which gives me the right boolean result (T/F):
=AND(ISBLANK(INDIRECT("C"&ROW())),ISBLANK(INDIRECT("D"&ROW())),ISBLANK(INDIRECT("E"&ROW())), ... etc. ... )
Placing this formula somewhere outside my data range works perfect, but it just doesn't work with conditional formatting.

Am I doing something wrong? Maybe the settings?

regards
Pete

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 12:15:23 -0700 (PDT) schrieb Pete:

One example:
fill a whole row from e.g. A1:G100 where the cells C to G are empty
I managed to find a formula which gives me the right boolean result (T/F):
=AND(ISBLANK(INDIRECT("C"&ROW())),ISBLANK(INDIRECT("D"&ROW())),ISBLANK(INDIRECT("E"&ROW())), ... etc. ... )
Placing this formula somewhere outside my data range works perfect, but it just doesn't work with conditional formatting.

why not trying:
=COUNTBLANK(C1:G1)=5


Regards
Claus Busch

 

[Pete]

Hi Pete,

Am Thu, 3 May 2012 12:15:23 -0700 (PDT) schrieb Pete:


why not trying:
=COUNTBLANK(C1:G1)=5


Regards
Claus Busch

Thanks, but I want to keep the formula variable and valid for any row.

So I modified your formula a little:

=COUNTBLANK(INDIRECT("B"&ROW()):INDIRECT("D"&ROW()))=3

but Excel tells me this:
"You may not use reference operators (such as unions, intersections, and ranges) or array contants for conditional formatting criteria."

and I even tried this one:

=EXACT(TEXT(COUNTBLANK(INDIRECT("B"&ROW()):INDIRECT("D"&ROW())),0),"3")

but I get the same message.

Well, I know that Excel don't work the same way as any type of programming/scripting language, but the logics should work.

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 12:36:17 -0700 (PDT) schrieb Pete:

Thanks, but I want to keep the formula variable and valid for any row.

So I modified your formula a little:

=COUNTBLANK(INDIRECT("B"&ROW()):INDIRECT("D"&ROW()))=3

in CF the formula will adapt for each row.
Select all cells from B1 & end and use the formula:
=COUNTBLANK(B11)=3


Regards
Claus Busch

 

[Pete]

Hi Pete,

Am Thu, 3 May 2012 12:36:17 -0700 (PDT) schrieb Pete:


in CF the formula will adapt for each row.
Select all cells from B1 & end and use the formula:
=COUNTBLANK(B11)=3


Regards
Claus Busch

WOW. Thank you! I didn't even think this could work.
I always have in mind how program languages work, that means "variables".

That would have been my second question:
How do I tell CF which cell to start with or how does CF actually look through all cells specified?

Example:
A Table looks like this and I put CF on A1:E3

# A B C D E ...
1 x a b c x
2 x d e f z
3 y g h i y
....

Now I want every row filled e.g. red, where column A and column E are different.
In this case row 2 should be marked red (-> A2 <> E2, x <> z)
Does Excel/CF have something like a "pointer" or "counter" like for example the popular "i" in loops e.g.: i=1, i=2, i=3, ... or in this case:
"look in first cell, check formula", "look in second cell, check formula", ..., "go to first cell in second row, check formula", ...

I just don't really understand, how formulas are evaluated in CF.

 

[Pete]

WOW. Thank you! I didn't even think this could work.
I always have in mind how program languages work, that means "variables".

That would have been my second question:
How do I tell CF which cell to start with or how does CF actually look through all cells specified?

Example:
A Table looks like this and I put CF on A1:E3

# A B C D E ...
1 x a b c x
2 x d e f z
3 y g h i y
...

Now I want every row filled e.g. red, where column A and column E are different.
In this case row 2 should be marked red (-> A2 <> E2, x <> z)
Does Excel/CF have something like a "pointer" or "counter" like for example the popular "i" in loops e.g.: i=1, i=2, i=3, ... or in this case:
"look in first cell, check formula", "look in second cell, check formula", ..., "go to first cell in second row, check formula", ...

I just don't really understand, how formulas are evaluated in CF.

P.S. When I see something like "COUNTBLANK(B11)=3" I always think that only row one is checked and CF stops right after that, because the row number is fixed (B11) and not variable (Bxx, with x being a variable representing the current row)

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 13:06:47 -0700 (PDT) schrieb Pete:

That would have been my second question:
How do I tell CF which cell to start with or how does CF actually look through all cells specified?

Example:
A Table looks like this and I put CF on A1:E3

# A B C D E ...
1 x a b c x
2 x d e f z
3 y g h i y
...

Now I want every row filled e.g. red, where column A and column E are different.
In this case row 2 should be marked red (-> A2 <> E2, x <> z)

select A1:E3 => CF => Formula =>
=$A1<>$E1
So A and E are absolute the whole row fill be filled


Regards
Claus Busch

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 13:06:47 -0700 (PDT) schrieb Pete:

That would have been my second question:
How do I tell CF which cell to start with or how does CF actually look through all cells specified?

the formula in CF always have to be referred to the active cell


Regards
Claus Busch

 

[Pete]

Hi Pete,

Am Thu, 3 May 2012 13:06:47 -0700 (PDT) schrieb Pete:


the formula in CF always have to be referred to the active cell


Regards
Claus Busch

Well, I still have some difficulties understanding how CF works.

If I take the same table as in the earlier example and add
"=A1=E1" to CF on "A1:E3"
(whis is automatically converted to: $A$1:$E$3 by Excel for any reason) only cells in the first column are marked:
A1 (="x" == E1="x") and C1 (="y" == C3 = "y") and cells E1 and C3 stay without background color, although the formula is still valid.

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 13:13:44 -0700 (PDT) schrieb Pete:

P.S. When I see something like "COUNTBLANK(B11)=3" I always think that only row one is checked and CF stops right after that, because the row number is fixed (B11) and not variable (Bxx, with x being a variable representing the current row)

Select all cells that should be formatted. Select B1 & end. B1 now is
active cell. =COUNTBLANK(B11)=3 refers to that cell and for all
selected cells the formula will be adapted. In CF you only see this
starting formula.
What Excel version do you use?


Regards
Claus Busch

 

[Pete]

Hi Pete,

Am Thu, 3 May 2012 13:13:44 -0700 (PDT) schrieb Pete:


Select all cells that should be formatted. Select B1 & end. B1 now is
active cell. =COUNTBLANK(B11)=3 refers to that cell and for all
selected cells the formula will be adapted. In CF you only see this
starting formula.
What Excel version do you use?


Regards
Claus Busch

I'm using: MSO 2010 (V 14.0) 64-bit (EN)

By the way, do you have an idea, why my first formula works fine in the sheet, but not in CF?

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 13:38:30 -0700 (PDT) schrieb Pete:

By the way, do you have an idea, why my first formula works fine in the sheet, but not in CF?

in CF the formula has to be reffered to the active cell. You can't use
INDIRECT in the formula.
Please have a look in both sheets, I hope that will show you the working
of CF:
http://www.claus-busch.de/Excel/Pete.xls


Regards
Claus Busch

 

[Pete]

in CF the formula has to be reffered to the active cell. You can't use
INDIRECT in the formula.
Please have a look in both sheets, I hope that will show you the working
of CF:
http://www.claus-busch.de/Excel/Pete.xls

Thank you very much for this example!

Select all cells that should be formatted. Select B1 & end. B1 now is
active cell. =COUNTBLANK(B11)=3 refers to that cell and for all
selected cells the formula will be adapted. In CF you only see this
starting formula.

Now I see the link between CF and drag-copying a formula inside the sheet. The "$" just works the same way (fixed row/column).
I think I do now understand the concept, but I'll have to try a few things out to see if I really got it.

But it's a real pity that INDIRECT does not work. I use this function very often, since at the time I type the formula I do not exactly know which cells will be affected.
For example one time its 3 cells to the right and 2 cells down (relative to the current). Next time it will be different.
E.g. in cell $M$1 I have the number representing the amount of rows and in $N$1 a number representing the amount of columns. M1=2 (go 2 rows down/up), N1=3 (go 3 rows left/right)

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 14:08:06 -0700 (PDT) schrieb Pete:

But it's a real pity that INDIRECT does not work. I use this function very often, since at the time I type the formula I do not exactly know which cells will be affected.
For example one time its 3 cells to the right and 2 cells down (relative to the current). Next time it will be different.
E.g. in cell $M$1 I have the number representing the amount of rows and in $N$1 a number representing the amount of columns. M1=2 (go 2 rows down/up), N1=3 (go 3 rows left/right)

INDIRECT doesn't work, because you don't use INDIRECT. The formula
refers to the active cell and will be adapted for all selected cells.
And the formula has nothing to do with the range you will format.
The range that should be formatted you can fix with your selection or
later in CF.


Regards
Claus Busch

 

[Claus Busch]

Hi Pete,

Am Thu, 3 May 2012 23:21:58 +0200 schrieb Claus Busch:

The range that should be formatted you can fix with your selection or
later in CF.

or you can change the range with the format painter.


Regards
Claus Busch

 

[Pete]

INDIRECT doesn't work, because you don't use INDIRECT. The formula
refers to the active cell and will be adapted for all selected cells.
And the formula has nothing to do with the range you will format.
The range that should be formatted you can fix with your selection or
later in CF.

OK, now I see the point and my misunderstanding.
What I was trying to do was using INDIRECT in an active cell referencing to itself, which is a circular reference. I just tried "=INDIRECT("A"&ROW())" in cell A1 which threw me an error message.
Many things have become clear now thanks to your patience, examples and explanations.