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

 

[jtakw]

Hi,

Don't know if this will help you, but you've got a lot of "redundant" IF tests in your Loong IF statement, it can be substantially shortened, and I've added the last two arguments in Post #8 :

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