Function to find a match a given result

[Guest]

Column 'Q' contains a list of submission dates

Column 'I' contains 3 geographic areas:- new-extension, both and re-config

I need to post in columns 'R' & 'S' the dates from column 'Q' when the following conditions prevail:

When column 'I' contains 'new-extension' or 'both' then column 'R' should show the date from Column 'Q
When column 'I' contains 're-config' or 'both' then column 'S' should show the date from Column 'Q
When no date is shown, then nothing should be indicated in the cell

Is there a formula/function to match things

Any help would be appreciated

Thank

Ando

 

[Vasant Nanavati]

Hi:

(Untested):

In cell R1:

=IF(OR(NOT(ISERROR(FIND("new-extension",I1))),NOT(ISERROR(FIND("both",I1))))
,Q1,"")

In cell S1:

=IF(OR(NOT(ISERROR(FIND("re-config",I1))),NOT(ISERROR(FIND("both",I1)))),Q1,
"")

FIND is case-sensitive. If this is a problem, use SEARCH.

Regards,

Vasant.

 

[Guest]

Hi Vasant

Thanks for that, works well, except in the following condition:

Column 'I' contains an area name (new-extension/both/re-config) and there is no date indicated in the the date column 'Q' it produces '00-Jan-00', can we avoid that

Thanks

Ando

----- Vasant Nanavati wrote: ----

Hi

(Untested)

In cell R1

=IF(OR(NOT(ISERROR(FIND("new-extension",I1))),NOT(ISERROR(FIND("both",I1)))
,Q1,""

In cell S1

=IF(OR(NOT(ISERROR(FIND("re-config",I1))),NOT(ISERROR(FIND("both",I1)))),Q1
""

FIND is case-sensitive. If this is a problem, use SEARCH

Regards

Vasant

 

[Harlan Grove]

Thanks for that, works well, except in the following condition:-

You do realize change orders cost more?

Column 'I' contains an area name (new-extension/both/re-config) and
there is no date indicated in the the date column 'Q' it produces
'00-Jan-00', can we avoid that?

R1:
=IF(AND(Q1,SUBSTITUTE("new-extension/both",I1,"")<>"new-extension/both"),
Q1,"")

S1:
=IF(AND(Q1,SUBSTITUTE("both/re-config",I1,"")<>"both/re-config"),Q1,"")

 

[Guest]

What ya talking about 'change orders cost more'???

Still doesn't work when there is no date input in cell Q1, still gives a response of 00-JAN-00 in R1 when only area equal either 'new-extension' or 'both'. Any other suggestions...

Thanks anyway...

 

[Guest]

Can anyone pls help

I'm realy need to resolve this today to issue my reports

Thanks...

 

[Harlan Grove]

...
...

Still doesn't work when there is no date input in cell Q1, still gives a
response of 00-JAN-00 in R1 when only area equal either 'new-extension'
or 'both'. Any other suggestions...

Sorry. I learned something new. If Q1 is blank, then IF(Q1,1,0) will return 0,
but IF(AND(Q1),1,0) returns #VALUE! and IF(AND(Q1,1),1,0) returns 1. I love XL!

Try these formulas.

R1:
=IF(AND(N(Q1),SUBSTITUTE("new-extension/both",I1,"")<>"new-extension/both"),
Q1,"")

S1:
=IF(AND(N(Q1),SUBSTITUTE("both/re-config",I1,"")<>"both/re-config"),Q1,"")

 

[Guest]

Harlan

Works a treat! Thanks a lot for your help

Andos