strange conditional format behavior

[Ernst Schuurman]

Hi Execel Guru's,

I have created a Worksheet that shows if laboratory results are available in
my database.
To do so there are 3 components:

1. the year plan (in column A and B;
starting from row 11 with 2007 jan 1 in A11;
and a samplecode in B11)
the samplecode is blank or A,B,C,D,E,F,G.

2. a lookuptable
with row 1 left blank, headerrow=2 with labcode in column F-->DO
and the 7 samplecodes in E3:E9
and markers ("X"-ses) at the intersection of the samplecodes (rows)
and labcodes (columns)

3. the data retreved from the database.
starting as E11 with the date and the values for the labcode in the
corresponding column
with the lookuptable.

4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give
ther rownumber of the
corresponding samplecode in the lookuptable for that day.

5. formulacolum (D) with formula: =COUNTA(F11O11) in D11 to count the
number of results for that day
as well as =COUNTA(F3O3) in front of the lookuptable to count te
expected number of results

D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-->
green, and
=INDIRECT("R"&C11&"C"&COLOMN();0)<>D11-->red
witch indicates if te results meet the plan.

So far all go's well.
Now I want for each labcode in a row to do the same, witch is indicating
green if th result meets the plan,
and red if there is no value.

To do so seemes easy, use the next formula in cell F11:
=IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)),"",AND _
(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBLANK(F11))))
if I put this formula in a cell it returns true or false as expected, but
when put in the formula aerea from CF,
nothing happens.
I also tested the two parts within the "AND" section separate, they work
well.
I als substituted one of them with true, again no result.
I do'nt understand this animore, anyone of you???

I hope there is an answere or explanation, and the problem is clear enough.

regards,

Ernst Schuurman

 

[T. Valko]

It's hard for the average user to understand this, but...

The problem is that this expression returns an array:

COLUMN(F:F)

Even though the array is a 1 element array, it's still an array. I can't
explain why it works as a sheet function (might be related to the implicit
intersection rule) but it won't work as is with CF.

You don't need this:

=IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)),"",

Try one of these:

=AND(INDIRECT("R"&$C11&"C"&INDEX(COLUMN(F:F),1),0)="X",NOT(ISBLANK(F11)))

=AND(INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X",NOT(ISBLANK(F11)))

I'm not sure why you're using this:

NOT(ISBLANK(F11))

If as you say, F11 contains a COUNTA formula then NOT(ISBLANK(F11)) will
*always* be TRUE.

So, you should be able to use:

=INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X"

 

[T. Valko]

So, you should be able to use:

=INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X"

Ooops! I copied the above formula from the longer version and didn't "clean"
it correctly. Should be:

=INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),0)="X"

 

[Ernst Schuurman]

Hi Biff,

Thanks for your answer.
You are absolutely wright about the column stuff, I even do'nt need it at
all, because
=INDIRECT("R"&$C11&"C,0)="X" will do exactly what I need.

About the NOT(ISBLANK(F11)) part:
Cell F11 is where the part with the imported results starts.
The lookup table tells me that for that day (2007 jan 1 for row 11) if the
samplecode that day has an "X" in column F
if that is true and F11 is not empty the cell colors green,
But when there is no value in F11 the cell colors red to indicate that a
result is missing.

I just changed the formula in the CF and it worked great.
So this version works as I need:

=IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",AND(INDIRECT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11))))

I do'nt know why I didn't see for myself that I could do without using the
absolute column in the indirect part.

Thanks so much for triggering me.
You are great as al the others in this forum.
I forgot to tell that I read these posts for some time now and I leared
alot.
It even changed my style regarding worksheet devellopment.

I wish you and all the others a happy 2008 and hope to read much more
interesting posts from and with you.

regards,

Ernst

 

[T. Valko]

Glad you got it worked out. Thanks for feeding back!

I just changed the formula in the CF and it worked great.
So this version works as I need:
=IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",AND(INDIRECT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11))))

If the formula above is what you're using for the CF, it will work but you
don't need the IF:

=IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",

This is all you need:

=AND(INDIRECT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11)))

You can even reduce it to:

=AND(INDIRECT("R"&$C11&"C,0)="X",F11<>"")

 

[Mouse Nest]

I have had a very similar problem. I believe this to be due to the fact that intersects are not supported in Excel conditional formatting.

If you use a range name for say a row in a normal Excel formula, then it uses the value for the current column by default. Thus it applies an implicit intersection operation.

If you do the same in conditional formatting formulae, which do not support intersections, Excel does not complain like it does with explicit intersections, but it just doesn't work as you would expect when used with ANDs and ORs. Strangely, whether it works or not seem to depend on the number of ANDed or ORed conditions. In my case two ORed conditions work, three did not!

Import the same formulae to Open Office, just to check you are not going mad, and the same conditional formatting formula works every time.

In Excel replace the range names by absolute or relative references, and everything works.

Definately a bug! Sigh!





Ernst Schuurman wrote:

strange conditional format behavior
27-Dec-07

Hi Execel Guru's

I have created a Worksheet that shows if laboratory results are available in
my database
To do so there are 3 components

1. the year plan (in column A and B
starting from row 11 with 2007 jan 1 in A11
and a samplecode in B11
the samplecode is blank or A,B,C,D,E,F,G

2. a lookuptabl
with row 1 left blank, headerrow=2 with labcode in column F-->D
and the 7 samplecodes in E3:E
and markers ("X"-ses) at the intersection of the samplecodes (rows
and labcodes (columns

3. the data retreved from the database
starting as E11 with the date and the values for the labcode in the
corresponding colum
with the lookuptable

4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give
ther rownumber of th
corresponding samplecode in the lookuptable for that day

5. formulacolum (D) with formula: =COUNTA(F11O11) in D11 to count the
number of results for that da
as well as =COUNTA(F3O3) in front of the lookuptable to count te
expected number of result

D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-->
green, an
=INDIRECT("R"&C11&"C"&COLOMN();0)<>D11-->re
witch indicates if te results meet the plan

So far all go's well
Now I want for each labcode in a row to do the same, witch is indicating
green if th result meets the plan
and red if there is no value

To do so seemes easy, use the next formula in cell F11
=IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)),"",AND
(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBLANK(F11)))
if I put this formula in a cell it returns true or false as expected, but
when put in the formula aerea from CF
nothing happens
I also tested the two parts within the "AND" section separate, they work
well
I als substituted one of them with true, again no result
I do'nt understand this animore, anyone of you??

I hope there is an answere or explanation, and the problem is clear enough

regards

Ernst Schuurman

EggHeadCafe - Software Developer Portal of Choice
Bing Search RSS with Silverlight 3 RIA Domain Service
http://www.eggheadcafe.com/tutorial...7-17e5f15f4c9a/bing-search-rss-with-silv.aspx