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 PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. 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 *

*