Calculated value based on IF Statement

DSTUART

New Member
Joined
Nov 21, 2011
Messages
9
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
 
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")))))))
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top