Thursday, 15 March 2012

How to show a column value based on other columns in a SharePoint Custom List

In one of the SharePoint list i was asked to provide a custom value based on the values inputed on the other columns. You might have faced a similar situation sometimes, this was thought to be a customisation, but i found that this could easily be acheived by using formula column in SharePoint.

I would run through the steps here :
Column 1
Column 2
Column 3 (Formula Column)
Value 1
Value 2
If Value in both Columns =”OK”
If  No Values in both columns =”Not Applicable”
 Value 1
 Value


So if Column1 and 2 has both values then Column 3 value = "OK" else "Not Applicable"

I tried couple of variations and was able to find a solution for this using the formula below :
=IF(COUNTA([Column 1],[Column 2])=0, "Not Applicable", "OK")

Step 1.
Create a new column in a list
Step 2.
Select the "Type of Information as" :
Calculated (calculation based on other columns)
Step 3.
In the Formula column insert the appropriate columns and add the formula (you may have to edit the formula based on the columns you are having)
Step 4.
Select the datatype returned from the formula (the values returned as results, in this case "single line of text" as i have given text values ("Not Applicable", "Yes")
Step 5.
Click OK and add the column


The page will throw validation errors if there are any problems with the formula you input.


You can use "CountA" parameter to check and count on non-blank columns, you can use this formulat in differnt variance to achieve the specific need you have.

Also Refer to the microsoft site for other formula parameters :

No comments: