Question of Complex IF statement

[menor59]

Hello all. I hope u had a great 4th..

So heres my Code:

=IF(G3<>"",IF(COUNTIF(Prowatch!G:G,G3),(D3)&" IS ACTIVE",(D3)&" IS NOT ACTIVE (Update Owner)"),"")

So G3 Contains an Email Address...lets say abc@gmail.com

so in G3 IF abc@gmail.com is Found on Sheet Prowatch!G:G is then takes D3 and says: abc is ACTIVE, if its not found on prowatch!G:G it then says abc is not active (Update owner)

THIS WORKS perfectly...

but what i need to add is IF N/A@gmail.com is in G3 IGNORE.....


in Closing example:

G3 = N/A@gmail.com Readout IGNORE email is FIXED static it will alwayb be N/A
G3 = anything@gmail.com, Search Range on prowatch!G:G, if found value of D3 IS ACTIVE - email is dynamic...can be steve@gmail.com, or tom@gmail.com
G3 = someboddy@gmail.com, Search Range on prowatch!G:G if not found value of D3 IS NOT ACTIVE - email is dynamic can be jim@gmail.com

Any help would be appreciated

 

[Leandrial]

Hi

im not sure i understand this correctly, so im trying with a mixed answer/question reply

But if i understand you correct, then the N/A@gmail weight the most... So wouldn't doing a series of If statements do the trick.. like

=IF(G3<>"", IF(G3="N/A@gmail.com", "IGNORE", IF(COUNTIF.... the rest of your original code

 

[Leandrial]

=IF(G3<>""; IF(G3="N/A@gmail.com"; "IGNORE"; IF(COUNTIF(Prowatch!G:G;G3);(D3)&" IS ACTIVE";(D3)&" IS NOT ACTIVE (Update Owner)"));"")

 

[menor59]

Too many arguments

=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[OWNER.1]]="N/A@gmail.com", "IGNORE", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE"),"")

You have ; those are acceptable?

ok I Did this"

=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[EMAIL.1]]="N/A@gmail.com", "IGNORE",IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")))

So where N/A@gmail.com is the Cell says FALSE not Ignore

 

[menor59]

One More Scenerio. Sorry for the Inconvience again...

=IF(Owners[@[EMAIL.1]]<>"", IF(Owners[@[EMAIL.1]]="N/A_Hiring@gmail.com",(Owners[@[OWNER.1]])&  " is IGNORED", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")),"")

I think i need an OR statement
currently I have in the code: IF(Owners[@[EMAIL.1]]="N/A@gmail.com",(Owners[@[OWNER.1]])& " is IGNORED"
if N/A_Hiring@gmail.com OR N/A_Facilities@gmail.com OR N/A_Reception@gmail.com
appear continue with the code...

Comments?

 

[Leandrial]

Too many arguments

=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[OWNER.1]]="N/A@gmail.com", "IGNORE", IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE"),"")

You have ; those are acceptable?

ok I Did this"

=IF(Owners[@[EMAIL.1]]<>"",IF(Owners[@[EMAIL.1]]="N/A@gmail.com", "IGNORE",IF(COUNTIF('Prowatch Import'!E:E,Owners[@[EMAIL.1]]),(Owners[@[OWNER.1]])&" IS ACTIVE",(Owners[@[OWNER.1]])&" IS NOT ACTIVE")))

So where N/A@gmail.com is the Cell says FALSE not Ignore

It looks like the @owner.1 for the N/A@gmail.com has changed to @Email.1 in your last bit of code.. could that be why it says False??

 

[Leandrial]

Hi Menor


I must admit that i keep loosing track of your mail variables etc

I wont mind helping you more with this (if I can ofc) but i might very well need to c your actual workbook... So if it isn't sensitive information, can you post it for me to have a look and thereby have a better understanding of your dataset??


Best regards


Kasper