|
conditional formatting formula
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 |
| All times are GMT +1. The time now is 01:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com