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(INDIR ECT("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<"")


--
Biff
Microsoft Excel MVP


"Ernst Schuurman" wrote in message
...
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(INDIRE CT("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" schreef in bericht
...
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"


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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