SharePoint 2010 Task List Validation: Comparing Two Columns, Status and % Complete

Did you know that SharePoint supports list validation in addition to column validation? I’ve seen the option 100 times before but it never clicked before I actually needed to use it. In this case, I needed to make sure that anyone setting a task item Status column to Completed” was also filling setting the % Complete column to 100%. Or, alternatively, if they were setting the % Complete column to 100%, they also set the task Status to Completed.

Need to do the same? Here you geaux:


Note that this simple formula doesn’t care about the percentages of any other status. If you’d like that functionality, check out the solution on this TechNet post.

Chrissy is a Cloud and Datacenter Management MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, and holds a number of certifications, including those relating to SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SharePoint
4 comments on “SharePoint 2010 Task List Validation: Comparing Two Columns, Status and % Complete
  1. John says:

    You rock! Thanks for posting. Such an end user and SP designer pain solved so simply. Well done.

  2. Mark says:

    If I use this formula, I get a Yes/No answer.
    Also when I select number output.

    This is my formula (Netherlands uses ";")

    =OR(IF(AND(Progress<>"Completed";[%Completed]<100%);TRUE;FALSE);IF(AND(Progress="Completed";[%Completed]=100%);TRUE;FALSE))

  3. Bryce says:

    I need to enforce Uniqueness with two columns. I am making a project priority ranking system. Each "process area" like finance will rank their projects from (1-25) and each process areas projects must have a unique rank #.

    I need to enforce uniqueness for both the "process area" column and the "project ranking" column
    Example: Process Area "Finance" can not rank two projects with a #1 ranking.
    But if Process Area is "Finance" and they rank a project "Project Rank" = 1 then Process area "Customer Service" should also be able to set "Project Rank" as #1 as well

    For example: If the Column "Process Area" Finance is selected

  4. Peter says:

    This was very useful thanks, but is there any way that you can set several completely independent formula within list validation. i.e. One rule applies to columns 1, 5 and 6, say and another rule that only involves columns 2 and 4 for example?

Leave a Reply

Your email address will not be published. Required fields are marked *

*