Showing posts with label Sharepoint library/list. Show all posts
Showing posts with label Sharepoint library/list. Show all posts

Monday, May 21, 2012

Using Calculated Column with maximum number of conditions(IF).

I am just taking an example in which Status column is an choice column with values Choice1 , choice2 and so on. I am assigning the color value based on the selection of status column value.

The Code is :-

=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))
For example, if the [Status] column contains Choice5, the output of the formula will be DarkCyan.

You cannot have more than seven nested IF statements in a calculated column.

But there is a worker process, you can do this by using the "&" symbol. Just concatenate these two formulas.

It will only work when the If statements are not nested. Now , i am giving an example of 15 IF functions...!! Check this...!!

The Code is :-

=IF([Status]=”",”Black”,IF([Status]=”Choice1″,”Red”,IF([Status]=”Choice2″,”Gold”,IF([Status]=”Choice3″,”Green”,IF([Status]=”Choice4″,”DarkBlue”,IF([Status]=”Choice5″,”DarkCyan”,IF([Status]=”Choice6″,”DarkRed”,IF([Status]=”Choice7″,”Gray”,”"))))))))&IF([Status]=”Choice8″,”MediumSlateBlue”,IF([Status]=”Choice9″,”SpringGreen”,IF([Status]=”Choice10″,”MidnightBlue”,IF([Status]=”Choice11″,”Sienna”,IF([Status]=”Choice12″,”SlateGray”,IF([Status]=”Choice13″,”OliveDrab”,IF([Status]=”Choice14″,”Gray”,”")))))))

Yeah...!! Finally the code worked...!!
Check out this one..!!