1

I'm new to SharePoint lists and prefer not to work with them, but my employer has decided to utilize it. The process/formula I have in excel works, but not in SharePoint lists.

We need a Calculated column that shows "Incomplete" if one or more of the cells are empty

The formula I have is:

=IF(OR(ISBLANK(B2),ISBLANK(C2),ISBLANK(D2),ISBLANK(E2),ISBLANK(F2),ISBLANK(G2),ISBLANK(H2),ISBLANK(I2),ISBLANK(J2)),"Incomplete","Complete")

I know for SharePoint, I use the column names.

The question I have in how on earth do I translate this to SharePoint language.

Any help would be gratefully received.

Improve this question
1
  • Create a calculated column, and replace B2/C2 etc with actual column name
    – Gaurravs
    Commented Jul 12, 2021 at 11:42

2 Answers 2

Reset to default
0

Please try this which is based on your inputs.

=IF(OR(ISBLANK(B2), OR(ISBLANK(C2), OR(ISBLANK(D2), OR(ISBLANK(E2), OR(ISBLANK(F2), OR(ISBLANK(G2), OR(ISBLANK(H2), OR(ISBLANK(I2), ISBLANK(J2))))))))), "Incomplete", "Complete")
Improve this answer
0

For you requirements, you need to create a Calculate column in list.

Follow below articles for creating calculate column in SharePoint list:

  1. Calculate data in lists or libraries
  2. How to create a Calculated Column in a SharePoint list or library

Syntax for the formula in SharePoint is similar to what you are using in Excel currently.

Also, you are correct that instead of B2, C2, D2 etc., you have to use the display names of SharePoint list columns.

The current formula you added to your question should work on SharePoint as it is.

Few points to remember:

  1. If there are spaces in display name of column, surround it by [] in formula, like:

    =IF(OR(ISBLANK([Column 1]), ISBLANK([Column 1]), ISBLANK([Column 1]), ISBLANK(E2), ISBLANK(F2), ISBLANK(G2), ISBLANK(H2), ISBLANK(I2), ISBLANK(J2)), "Incomplete", "Complete")

  2. If your formula is throwing syntax error even if everything in formula is correct, try using semicolon(;) instead of comma(,).

    Source: Using date commands in calculated fields

Also, check below Microsoft official documentations for calculated column formulas:

  1. Examples of common formulas in lists
  2. IF function
  3. OR function
  4. IS functions - ISBLANK
Improve this answer

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.