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:

=OR(IF(AND(Status<>"Completed",[% Complete]<100%),TRUE,FALSE),IF(AND(Status="Completed",[% Complete]=100%),TRUE,FALSE))

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 & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SharePoint
5 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 ";")


  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?

  5. Janet Johnson says:

    I sort of have the same thing I’m trying to do, but when the Status = “Completed” I need 2 separate columns to be filled in (Time spent AND Resolution) otherwise the form doesn’t save. I have this formula but it is not working correctly

    =IF(OR(AND([Task Status]=”Completed”,[Resolution]””),AND([Task Status]=”Completed”,[Time Spent] “”)),TRUE,FALSE)

    …any help would be greatly appreciated!!

Leave a Reply