Calculated value based on IF Statement

[DSTUART]

Hi Folks,

Sorry if this is a double post but my last one seems to have disappeared

Hope someone can help.

I'm using the following if statement to populate a calculated value in sharepoint. It ensures the values defined are present before populating the email calculated field.

IF(Category="Operations",IF([Sub-Category]="N/A",IF([Task Risk Level]="Low","David.Stuart@*******.com",
IF(Category="Operations",IF([Sub-Category]="N/A",IF([Task Risk Level]="Medium","David.Stuart@******.com",
IF(Category="Operations",IF([Sub-Category]="N/A",IF([Task Risk Level]="High","David.Stuart@******.com",
IF(Category="Maintenance",IF([Sub-Category]="Mechanical",IF([Task Risk Level]="Low","David.Stuart@******.com",IF(Category="Maintenance",IF([Sub-Category]="Mechanical",IF([Task Risk Level]="Medium","David.Stuart@******.com",IF(Category="Maintenance",IF([Sub-Category]="Mechanical",IF([Task Risk Level]="High","David.Stuart@******.com"))))))))))))

The result should be my email address will be stored depending on other values in the list. This is based on a Risk matrix we use.

What I'm finding is that the first set of values for Operations will populate the calculates field as required. But the second set based on Maintenance will not. The statement itself does not error when applied in Sharepoint so it does not see a problem. So Im assuming its to do with the IF Statement on the second set. Ive been trying multiple ways to get it to work but im not great with this. Anyone have an idea. There will be more to add to this after the Maintenance section.

Hope someone can help

Dave Stuart

 

[Nine Zero]

Try this out

=IF(OR(AND(Category="Operations",[Sub-Category]="N/A",OR([Task Risk Level]="Low",[Task Risk Level]="Medium",[Task Risk Level]="High")),AND(Category="Maintenance",[Sub-Category]="Mechanical",OR([Task Risk Level]="Low",[Task Risk Level]="Medium",[Task Risk Level]="High"))),"David.Stuart@*******.com","Something is empty")

Couldn't test but logically looks correct lol

 

[DSTUART]

Hi, Thanks for this, but im not sure its doing exactly what I need. The email address examples above will all be different for the end result and therefore assign tasks to the relevant people. This is why the sections need to be processed seperatly.

The code above works but is it not just checking if any of the values are present and applying the Set Email?

So for example if the values are

Operations
N/A
Low

then assign it to ABC@123.com

Operations
N/A
Medium
then assign it to DEF@456.com

Operations
N/A
High
then assign it to GHI@456.com

Then if none of these check for next set

Maintenance
Mechanical
Low
then assign it to JKL@456.com

etc....

 

[Nine Zero]

ohh i see ... i saw in the example you had the same email for every result so i figured it would be the same lol my bad... gimme a sec and ill fix it real quick

 

[Nine Zero]

Try this one out

=IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="Low"),"ABC@*******.com",IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"123@******.com",IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="High"),"DEF@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="High"),"789@******.com","something is off"))))))

 

[DSTUART]

That is awesome my friend. Thank you so much.

 

[Nine Zero]

No problem, glad i could help

 

[DSTUART]

Got one final question. Do you know if theres a limit on IF statements?

This is fine and works great

=IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="Low"),"ABC@*******.com",IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"123@******.com",IF(AND(Category="Operations",[Sub-Category]="N/A",[Task Risk Level]="High"),"DEF@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Mechanical",[Task Risk Level]="High"),"789@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Systems Instruments and Electrical",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Maintenance",[Sub-Category]="Systems Instruments and Electrical",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Maintenance",[Sub-Category]="Systems Instruments and Electrical",[Task Risk Level]="High"),"789@******.com",IF(AND(Category="Cranes",[Sub-Category]="N/A",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Cranes",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Cranes",[Sub-Category]="N/A",[Task Risk Level]="High"),"789@******.com",IF(AND(Category="Metering",[Sub-Category]="N/A",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Metering",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Metering",[Sub-Category]="N/A",[Task Risk Level]="High"),"789@******.com",IF(AND(Category="Inspection",[Sub-Category]="N/A",[Task Risk Level]="Low"),"456@*******.com",IF(AND(Category="Inspection",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"GHI@******.com",IF(AND(Category="Inspection",[Sub-Category]="N/A",[Task Risk Level]="High"),"789@******.com",IF(AND(Category="Other",[Sub-Category]="N/A",[Task Risk Level]="Low"),"123@******.com","")))))))))))))))))))

but I have two more to add and it keeps erroring

IF(AND(Category="Other",[Sub-Category]="N/A",[Task Risk Level]="Medium"),"123@******.com",
IF(AND(Category="Other",[Sub-Category]="N/A",[Task Risk Level]="High"),"123@******.com",

I add these in then get error even though there is already one in for "Other". I am adding two extract brackets as well ))

 

[DSTUART]

Actually there must be. Just replace two values with these and it worked so ther has to be a limit.

 

[Nine Zero]

thats weird though... excel allows up to 64 nested IF statements, you currently only have 17 .... also allows up to 32767 characters per cell ... interesting