Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2 April 6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to the
spread sheet) nor for any later date, I want to put the name in red and bold
by means of conditional formatting. What conditional formatting formula do I
have to use for A4 to A40?

Jack Sons
The Netherlands

Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2 April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to
the
spread sheet) nor for any later date, I want to put the name in red and
bold
by means of conditional formatting. What conditional formatting formula do
I
have to use for A4 to A40?

Jack Sons
The Netherlands

Jack,

Sorry. You will need to change the NOW to TODAY.

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2
April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical
entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to
the
spread sheet) nor for any later date, I want to put the name in red and
bold
by means of conditional formatting. What conditional formatting formula
do
I
have to use for A4 to A40?

Jack Sons
The Netherlands

Bernie,

Thanks for your answer.

I also kept trying myself, off course. One minute before I saw your solution
I arrived at

=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)

which also works but is definitely much less elegant than your simple
formula.

Thanks again.

Jack.

"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2
April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical
entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to
the
spread sheet) nor for any later date, I want to put the name in red and
bold
by means of conditional formatting. What conditional formatting formula
do
I
have to use for A4 to A40?

Jack Sons
The Netherlands

Bernie,

In fact I arrived at
=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)<TODAY()
so I also changed your NOW in TODAY, but NOW works as well.

Jack.

"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2
April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical
entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking to
the
spread sheet) nor for any later date, I want to put the name in red and
bold
by means of conditional formatting. What conditional formatting formula
do
I
have to use for A4 to A40?

Jack Sons
The Netherlands

Jack,

Actually, NOW won't work for today's entries, since NOW() returns a decimal
that is greater than the integer that TODAY() returns, and the dates that
you have in row 2 are integers.

HTH,
Bernie
MS Excel MVP


"Jack Sons" wrote in message
...
Bernie,

In fact I arrived at
=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)<TODAY()
so I also changed your NOW in TODAY, but NOW works as well.

Jack.

"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2
April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical
entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking
to
the
spread sheet) nor for any later date, I want to put the name in red and
bold
by means of conditional formatting. What conditional formatting formula
do
I
have to use for A4 to A40?

Jack Sons
The Netherlands

Bernie,

Thanks, I did not notice.

Jack.

"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Jack,

Actually, NOW won't work for today's entries, since NOW() returns a
decimal
that is greater than the integer that TODAY() returns, and the dates that
you have in row 2 are integers.

HTH,
Bernie
MS Excel MVP


"Jack Sons" wrote in message
...
Bernie,

In fact I arrived at
=OFFSET(A4;25-ROW();MATCH(LOOKUP(REPT("z";255);4:4);4:4;0)-1)<TODAY()
so I also changed your NOW in TODAY, but NOW works as well.

Jack.

"Bernie Deitrick" <deitbe @ consumer dot org schreef in bericht
...
Jack,

=SUMPRODUCT(($E$2:$CR$2=NOW())*(E3:CR3<""))=0

HTH,
Bernie
MS Excel MVP

"Jack Sons" wrote in message
...
Hi all,

In row 2 I have dates, E2 contains April 4 2005, F2 April 5 2005, G2
April
6
2005 etc.
In A4 to A40 I have names and in E4 to CR40 I have an entry where an
appointment is made. For instance, in A7 I have "Maas" and I have an
appointment with Maas at April 6, then in G7 will be a non numerical
entry
(for instance "H 14.00 London" or some other text).

In the row of each name there should be an appointment for today or
any
future date, that is what I want to check.

If a name has no appointment for "today" (I mean the day I am looking
to
the
spread sheet) nor for any later date, I want to put the name in red
and
bold
by means of conditional formatting. What conditional formatting
formula
do
I
have to use for A4 to A40?

Jack Sons
The Netherlands