Lookup formula

[dchatrie]

I am seeking assistance with a formula, I am trying to show the status of the scope of a project. Please see attached my workbook. I would like the formula to search the status scope column and once there is a status scope open(requested) despite other status being approved or rejected, the formula should return "requested". If there are no request open(requested) then it should return "approved" if only approved appears or "rejected" if only rejected appears. If both occur at the same time (approved and rejected), i would like it to return nothing. If no request is made(that is the table is blank) I would like it to return "No scope change".

Thank you for any assistance that you can give.

 

[Sajan]

I am not clear on what you are requesting.

Can you upload your worksheet with realistic data (instead of blah blah)?

 

[SirJB7]

Hi, dchatrie!
Try changing your A1 formula to this:
="Scope Change"&" "&SI(CONTARA(D4:D9)=0;"None";SI(CONTAR.SI(D4:D9;"Requested")>0;"Requested";SI(CONTARA(D4:D9)=CONTAR.SI(D4:D9;"Approved");"Approved";SI(CONTARA(D4:D9)=CONTAR.SI(D4:D9;"Rejected");"Rejected";"WTF?")))) -----> in english: ="Scope Change"&" "&IF(COUNTA(D4:D9)=0,"None",IF(COUNTIF(D4:D9,"Requested")>0,"Requested",IF(COUNTA(D4:D9)=COUNTIF(D4:D9,"Approved"),"Approved",IF(COUNTA(D4:D9)=COUNTIF(D4:D9,"Rejected"),"Rejected","WTF?"))))
Regards!
PS: Pls replace :D by : D with no spaces

 

[Sajan]

Hi SirJB7,
That formula has a lot of smiley faces... that must be what is known as a happy formula!

-Sajan.

 

[SirJB7]

@Sajan
Hi!
I thought about editing it... but there's no replace feature for text converted to smilies... neither for any text... and you know I'm lazy... so...
Regards!
PS: Beginning to get a bit bothered with some of this changes...

 

[Haseeb A]

SirJB,

Place your formula with CODE

'

...

Should be OK

 

[SirJB7]

@Haseeb
Hi!
Yeap, Luke already advised about it, I always forget. Thanks.
Regards!

 

[SirJB7]

Hi, dchatrie!
Try changing your A1 formula to this:

="Scope Change"&" "&SI(CONTARA(D4:D9)=0;"None";SI(CONTAR.SI(D4:D9;"Requested")>0;"Requested";SI(CONTARA(D4:D9)=CONTAR.SI(D4:D9;"Approved");"Approved";SI(CONTARA(D4:D9)=CONTAR.SI(D4:D9;"Rejected");"Rejected";"WTF?")))) -----> in english: ="Scope Change"&" "&IF(COUNTA(D4:D9)=0,"None",IF(COUNTIF(D4:D9,"Requested")>0,"Requested",IF(COUNTA(D4:D9)=COUNTIF(D4:D9,"Approved"),"Approved",IF(COUNTA(D4:D9)=COUNTIF(D4:D9,"Rejected"),"Rejected","WTF?"))))

Regards!
PS: Pls replace :D by : D with no spaces

PS: Now not need to replace the smiley, just need a ticket to ride... thru the right.

 

[dchatrie]

Thank you, it was most helpful

 

[SirJB7]

Hi, dchatrie!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!