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"


--
Biff
Microsoft Excel MVP


"Ernst Schuurman" wrote in message
...
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(F11:DO11) in D11 to count the
number of results for that day
as well as =COUNTA(F3:DO3) 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(ISBL ANK(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