Random search

[roy.okinawa]

If column K3 is filled (date) and/or column AC3 if filled (date), I want the
text in column X3 to autopopulate in column AE3 as long as in matches the
mm/yyyy that I have entered in AE1.

Column X will always keep its data. Column AE can changed based on the date
entered in AE1.


AE1

12/2005

K3 X3 AC3
AE3
12/15/2005 Unrepairable 12/20/2005
Unrepairable
12/16/2005 Serviceable 12/21/2005
Serviceable

[roy.okinawa]

Column X will have a variety of text in it. Not just the two examples I gave.

"roy.okinawa" wrote:

> If column K3 is filled (date) and/or column AC3 if filled (date), I want the
> text in column X3 to autopopulate in column AE3 as long as in matches the
> mm/yyyy that I have entered in AE1.
>
> Column X will always keep its data. Column AE can changed based on the date
> entered in AE1.
>
>
> AE1
>
> 12/2005
>
> K3 X3 AC3
> AE3
> 12/15/2005 Unrepairable 12/20/2005
> Unrepairable
> 12/16/2005 Serviceable 12/21/2005
> Serviceable

[Pete]

Hi Roy,

trouble with IF again ?

Are you saying that AC3 (only) has to match the month and year in AE1,
or can EITHER K3 OR AC3 match with AE1? Can you guarantee that column X
will contain some text (i.e. not empty)?

Pete

[roy.okinawa]

Hi Pete,

Thanks for the assist. I'm getting better, but not there yet.

K3 or AC3 can match. K3 is the open date and AC3 is the closed date. They
may or may not open/close in the same month. That is why I want it to search
for the month match in AE1.

Column X may be empty if no remarks are required.

"Pete" wrote:

> Hi Roy,
>
> trouble with IF again ?
>
> Are you saying that AC3 (only) has to match the month and year in AE1,
> or can EITHER K3 OR AC3 match with AE1? Can you guarantee that column X
> will contain some text (i.e. not empty)?
>
> Pete
>
>

[Pete]

Roy,

enter the following formula in cell AE3:

=IF(X3="","",
IF(OR(
(AND(MONTH(AC3)=MONTH(AE$1),YEAR(AC3)=YEAR(AE$1))),
(AND(MONTH(K3)=MONTH(AE$1),YEAR(K3)=YEAR(AE$1))
)),X3,""))

As before, all one formula - I've just split it here to make it easier
to read. If you copy it down it will continue to reference AE1, but use
the values on other rows to determine the outcome.

Hope this helps,

Pete

[roy.okinawa]

Pete,

When I go to save the formula, it keeps saying there is an error in the
formula that was typed. It then highlights the last X3 in the formula.



"Pete" wrote:

> Roy,
>
> enter the following formula in cell AE3:
>
> =IF(X3="","",
> IF(OR(
> (AND(MONTH(AC3)=MONTH(AE$1),YEAR(AC3)=YEAR(AE$1))),
> (AND(MONTH(K3)=MONTH(AE$1),YEAR(K3)=YEAR(AE$1))
> )),X3,""))
>
> As before, all one formula - I've just split it here to make it easier
> to read. If you copy it down it will continue to reference AE1, but use
> the values on other rows to determine the outcome.
>
> Hope this helps,
>
> Pete
>
>