1

enter image description hereI know I have done this before, but I am at a different company now and can't look up how I did it. I have created a Project Tracker list with several columns, including Status, % Complete, and Completion Date.

I want to allow the "Completed" choice for Status only if the % Complete = 100and the Completion Date is not blank. I am not sure exactly what to put in the formula field for Column Validation. I think it's something like this:

[Status] = Completed if [% Complete] = 100% AND [Completion Date] = not blank

Improve this question

1 Answer 1

Reset to default
1

The % Complete field varies between 0 and 1. "Not blank" is represented by NOT(ISBLANK(field))

You can't do this under the column settings. You have to do it as a list validation. Go to List Settings, and under the General Settings section, click "Validation settings." Add this formula there:

=IF(Status="Completed",AND([% Complete]=1,NOT(ISBLANK([Completion Date]))),TRUE)

Improve this answer
11
  • Hi Erin - Thanks very much for your answer. I am still having trouble. I pasted in exactly what you had, and got the same error. I then tried about 12 different variations (I am not a programmer, so the syntax is a bit hit and miss with me). This is the latest one I tried: [Status]=“Completed” if ([% Complete]=1,NOT(ISBLANK([Completion Date])),TRUE) ..................... Can you please tell me what I need to change? I just want to make sure a person can't choose the Completed status unless they have marked 100% for % Complete and entered a Completion Date. Thanks again for your help! Norma
    – user3629539
    Commented Aug 26, 2016 at 13:57
  • You can't use if the way you wrote. Did you include the equal sign at the beginning? I tested this and don't get an error. Is your list a custom list, or did you start from a Tasks list?
    – Erin L
    Commented Aug 26, 2016 at 14:00
  • Boss needs something; will try again shortly. Thanks.
    – user3629539
    Commented Aug 26, 2016 at 14:02
  • I have a screen shot but I am not able to add it here...
    – user3629539
    Commented Aug 26, 2016 at 14:19
  • Can you add it to your original post?
    – Erin L
    Commented Aug 26, 2016 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.