1

I have a nested IF statement, in an AND statement. The IF statement ensures that IF an "Approval Date" is entered, a value must be entered in the "Total Budget Amount Approved" column.

My list has multiple columns to input an "approval date" and "total budget amount". I am attempting to replicate this nested IF(ISBLANK criteria a total of 6 times. The cumulative budget amount cannot exceed $1000, hence the AND statement.

So far I have:

=AND([Cumulative TOTAL (not to exceed $1000)]<1001,IF(ISBLANK([(1) Approval Date]),TRUE,IF(ISBLANK([(1) Total Budget Amount Approved]),FALSE,TRUE)))

This works great.

However, when I attempt to add more, the validation results in an error. Any thoughts on how to correctly break this down? Below is unsuccessful attempt.

=AND([Cumulative TOTAL (not to exceed $1000)]<1001,IF(ISBLANK([(1) Approval Date]),TRUE,IF(ISBLANK([(1) Total Budget Amount Approved]),FALSE,TRUE,,IF(ISBLANK([(2) Approval Date]),TRUE,IF(ISBLANK([(2) Total Budget Amount Approved]),FALSE,TRUE,IF(ISBLANK([(3) Approval Date]),TRUE,IF(ISBLANK([(3) Total Budget Amount Approved]),FALSE,TRUE,IF(ISBLANK([(4) Approval Date]),TRUE,IF(ISBLANK([(4) Total Budget Amount Approved]),FALSE,TRUE,IF(ISBLANK([(5) Approval Date]),TRUE,IF(ISBLANK([(5) Total Budget Amount Approved]),FALSE,TRUE,IF(ISBLANK([(6) Approval Date]),TRUE,IF(ISBLANK([(6) Total Budget Amount Approved]),FALSE,TRUE)))

This is an extension of the response received in this post: If one column is not empty, require value in another column

UPDATE (09-20-24)

I tried using a code with the comma and semi colon however, both result in an error. See examples of the two codes below. I included the true names of the columns:

Code 1:

=AND([Cumulative TOTAL (not to exceed $1000)]<1001,IF(ISBLANK([(1) Approval Date of CNBA]),TRUE,IF(ISBLANK([(1) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(2) Approval Date of CNBA],TRUE,IF(ISBLANK([(2) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(3) Approval Date of CNBA]),TRUE,IF(ISBLANK([(3) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(4) Approval Date of CNBA],TRUE,IF(ISBLANK([(4) Amount of CNBA],FALSE,TRUE)),IF(ISBLANK([(5) Approval Date of CNBA]),TRUE,IF(ISBLANK([(5) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(6) Approval Date of CNBA]),TRUE,IF(ISBLANK([(6) Amount of CNBA],FALSE,TRUE)))

Code 2

=AND([Cumulative TOTAL (not to exceed $1000)]<1001;IF(ISBLANK([(1) Approval Date of CNBA]);TRUE;IF(ISBLANK([(1) Amount of CNBA]);FALSE;TRUE));IF(ISBLANK([(2) Approval Date of CNBA];TRUE;IF(ISBLANK([(2) Amount of CNBA]);FALSE;TRUE));IF(ISBLANK([(3) Approval Date of CNBA]);TRUE;IF(ISBLANK([(3) Amount of CNBA]);FALSE;TRUE));IF(ISBLANK([(4) Approval Date of CNBA];TRUE;IF(ISBLANK([(4) Amount of CNBA];FALSE;TRUE));IF(ISBLANK([(5) Approval Date of CNBA]);TRUE;IF(ISBLANK([(5) Amount of CNBA]);FALSE;TRUE));IF(ISBLANK([(6) Approval Date of CNBA]);TRUE;IF(ISBLANK([(6) Amount of CNBA];FALSE;TRUE)))

Improve this question

1 Answer 1

Reset to default
0

If you want to combine all conditions using AND, you can add those conditions in this format:

=AND(<condition1>,<condition2>,<condition3>)

Example:

=AND([Cumulative TOTAL (not to exceed $1000)]<1001,IF(ISBLANK([(1) Approval Date]),TRUE,IF(ISBLANK([(1) Total Budget Amount Approved]),FALSE,TRUE)),IF(ISBLANK([(2) Approval Date]),TRUE,IF(ISBLANK([(2) Total Budget Amount Approved]),FALSE,TRUE)),IF(ISBLANK([(3) Approval Date]),TRUE,IF(ISBLANK([(3) Total Budget Amount Approved]),FALSE,TRUE)),IF(ISBLANK([(4) Approval Date]),TRUE,IF(ISBLANK([(4) Total Budget Amount Approved]),FALSE,TRUE)),IF(ISBLANK([(5) Approval Date]),TRUE,IF(ISBLANK([(5) Total Budget Amount Approved]),FALSE,TRUE)),IF(ISBLANK([(6) Approval Date]),TRUE,IF(ISBLANK([(6) Total Budget Amount Approved]),FALSE,TRUE)))

Note:

  1. Sometimes comma(,) does not work in formula (it is based on language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
  2. Use correct display name of your SharePoint columns in above formula.
  3. Wrap column names inside [] if your column name has space in it. For example: [My Column Name].
  4. You can add up to 30 conditions inside single AND function.

Update from comments:

You are missing a lot of closing parentheses/brackets in your formula. Try closing all brackets in correct place. Check if below works for you:

=AND([Cumulative TOTAL (not to exceed $1000)]<1001,IF(ISBLANK([(1) Approval Date of CNBA]),TRUE,IF(ISBLANK([(1) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(2) Approval Date of CNBA]),TRUE,IF(ISBLANK([(2) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(3) Approval Date of CNBA]),TRUE,IF(ISBLANK([(3) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(4) Approval Date of CNBA]),TRUE,IF(ISBLANK([(4) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(5) Approval Date of CNBA]),TRUE,IF(ISBLANK([(5) Amount of CNBA]),FALSE,TRUE)),IF(ISBLANK([(6) Approval Date of CNBA]),TRUE,IF(ISBLANK([(6) Amount of CNBA]),FALSE,TRUE)))
Improve this answer
2
  • Thank you of working through this with me! I came across an error. Please refer to my "Update 09-20-24" comment, edited into the main post. Commented Sep 20, 2024 at 21:35
  • check updated answer. Commented Sep 21, 2024 at 3:29

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.