Cell search & reporting

Hi
Is there a formula that would search one cell for a month and then search a
range of other cells for a date and then look below and report into the
formula cell what is reported possibly in a different colour depending on
what is reported.
e.g.

A B C D
E F G
1 Date 31â?¦Month Decemberâ?¦Year 2007 2
3 MON TUE WED THU FRI SAT SUN
4 31 1 2 3 4 5 6
5 smith E E DO S E DO DO
6 jones DO N DO DO DO DO N
7
8
9
The cell in A9 would search cell B3 to find the month required, then would
search cells A4 to G4 for the correct date e.g. recognise that the required
date is the 3rd of january in cell D4. It would then look below in jones's
line and report back S in red.
Thanks in advance
Jason

Bernie
Sat Mar 08 12:25:25 CST 2008

What do you mean by: "search cell B3 to find the month required"? And how
do you know that the correct date is January 4?

Bernie



"Boenerge" <Boenerge@discussions.microsoft.com> wrote in message
news:3E87A9E9-F885-45F5-A28B-47D9A6D25FF4@microsoft.com...
> Hi
> Is there a formula that would search one cell for a month and then search
> a
> range of other cells for a date and then look below and report into the
> formula cell what is reported possibly in a different colour depending on
> what is reported.
> e.g.
>
> A B C D
> E F G
> 1 Date 31.Month December.Year 2007 2
> 3 MON TUE WED THU FRI SAT SUN
> 4 31 1 2 3 4 5 6
> 5 smith E E DO S E DO DO
> 6 jones DO N DO DO DO DO N
> 7
> 8
> 9
> The cell in A9 would search cell B3 to find the month required, then would
> search cells A4 to G4 for the correct date e.g. recognise that the
> required
> date is the 3rd of january in cell D4. It would then look below in
> jones's
> line and report back S in red.
> Thanks in advance
> Jason
>

Boenerge
Sat Mar 08 14:44:00 CST 2008

Hi Bernie,
I mean to search the cell for the month, which in this case is December, The
formula should then pick up the fact that 31 December 2007 is the beginning
of the week, working out cell D4 as the 3rd of January
Thanks
Jason

"Bernie Deitrick" wrote:

> What do you mean by: "search cell B3 to find the month required"? And how
> do you know that the correct date is January 4?
>
> Bernie
>
>
>
> "Boenerge" <Boenerge@discussions.microsoft.com> wrote in message
> news:3E87A9E9-F885-45F5-A28B-47D9A6D25FF4@microsoft.com...
> > Hi
> > Is there a formula that would search one cell for a month and then search
> > a
> > range of other cells for a date and then look below and report into the
> > formula cell what is reported possibly in a different colour depending on
> > what is reported.
> > e.g.
> >
> > A B C D
> > E F G
> > 1 Date 31.Month December.Year 2007 2
> > 3 MON TUE WED THU FRI SAT SUN
> > 4 31 1 2 3 4 5 6
> > 5 smith E E DO S E DO DO
> > 6 jones DO N DO DO DO DO N
> > 7
> > 8
> > 9
> > The cell in A9 would search cell B3 to find the month required, then would
> > search cells A4 to G4 for the correct date e.g. recognise that the
> > required
> > date is the 3rd of january in cell D4. It would then look below in
> > jones's
> > line and report back S in red.
> > Thanks in advance
> > Jason
> >
>
>
>

Bernie
Sat Mar 08 15:40:28 CST 2008

Jason,

The formula

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3,".",""),"Date
",""),"Month",""),"Year",""))

formatted for date, will return the date value found in the string

Date 31.Month December.Year 2007

(If that is truly how it is presented.)

Then you can use that value in a formula like this - let's assume that the
formula is in cell F3:

=DATE(YEAR(F3),MONTH(F3) + IF(DAY(F3)>D4,1,0),D4)

Not sure what you want to do after that...

Bernie



"Boenerge" <Boenerge@discussions.microsoft.com> wrote in message
news:6F4B028A-757F-41FD-BDB5-D13B88FEDAAE@microsoft.com...
> Hi Bernie,
> I mean to search the cell for the month, which in this case is December,
> The
> formula should then pick up the fact that 31 December 2007 is the
> beginning
> of the week, working out cell D4 as the 3rd of January
> Thanks
> Jason
>
> "Bernie Deitrick" wrote:
>
>> What do you mean by: "search cell B3 to find the month required"? And
>> how
>> do you know that the correct date is January 4?
>>
>> Bernie
>>
>>
>>
>> "Boenerge" <Boenerge@discussions.microsoft.com> wrote in message
>> news:3E87A9E9-F885-45F5-A28B-47D9A6D25FF4@microsoft.com...
>> > Hi
>> > Is there a formula that would search one cell for a month and then
>> > search
>> > a
>> > range of other cells for a date and then look below and report into the
>> > formula cell what is reported possibly in a different colour depending
>> > on
>> > what is reported.
>> > e.g.
>> >
>> > A B C D
>> > E F G
>> > 1 Date 31.Month December.Year 2007 2
>> > 3 MON TUE WED THU FRI SAT SUN
>> > 4 31 1 2 3 4 5 6
>> > 5 smith E E DO S E DO DO
>> > 6 jones DO N DO DO DO DO N
>> > 7
>> > 8
>> > 9
>> > The cell in A9 would search cell B3 to find the month required, then
>> > would
>> > search cells A4 to G4 for the correct date e.g. recognise that the
>> > required
>> > date is the 3rd of january in cell D4. It would then look below in
>> > jones's
>> > line and report back S in red.
>> > Thanks in advance
>> > Jason
>> >
>>
>>
>>