1

I am trying to figure out how to make a yes/no column named "completed" auto-fill "yes" when data has been entered and proofed. Column1 and Column2 in my sample formulas can either be a data enterer/proofer's name (text) or hours billed (number).

We can use either column for the formula but basically what I want to happen is when both the entered and the proofed columns are filled (name or hours), the "completed" cell will return "yes"(TRUE), or vise-versa (if either is blank it returns "no" (false), as seen in my sample formulas).

A few questions concerning this task:

  1. What is the syntax for a blank cell?

"EMPTY"? EMPTY? BLANK? Or must I use the ISBLANK function?

  1. What is the syntax for not equal to?

<>, like in excel? Does this work for both text and number cells in SharePoint?

  1. Which would be better for this formula, an OR statement identifying either cell is blank or an AND statement identifying both cells have a value (any value)?

Samples:

=IF(OR([Column1]=BLANK,[Column2]=BLANK),FALSE)

=IF(AND([Column1]<>BLANK,[Column2]<>BLANK),TRUE)

Improve this question

2 Answers 2

Reset to default
1

Here's a little "truth" table for three formulas:

  • Column1 is single line of text.
  • Column2 is number.
  • test1 =IF(ISBLANK(OR(Column1,Column2)),FALSE,TRUE) (wrong results)
  • test2 =IF(OR(ISBLANK(Column1),ISBLANK(Column2)),FALSE,TRUE)
  • test3 =NOT(OR(ISBLANK(Column1),ISBLANK(Column2)))

enter image description here

IF's that return true or false can be shortened to just the Boolean logic, or the NOT of that logic.

My preference is test3 if just returning a Boolean (true/false/yes/no) and test2 if the the IF is returning other messages or numbers instead of true/false.

Improve this answer
4
  • Thanks Mike! I received syntax errors for both of these and also tried [] brackets instead of () parenthesis, around the column names. I would like to add, however that column 1 and 2 will be consistently either text or number, I just have 2 options to choose between 4 columns, if that makes sense. I don't think that should cause an issue though. The result that I am looking for is in fact a "yes"/"no" response for the completed column return.
    – aaanjah
    Apr 24, 2018 at 18:04
  • I also tried these with your logic: =IF(NOT(AND(ISBLANK(Column1),ISBLANK(Column2)))),TRUE) =NOT(AND(ISBLANK(Column1),ISBLANK(Column2))),TRUE) Both returned a "syntax error or is not supported", like the ones above.
    – aaanjah
    Apr 24, 2018 at 18:18
  • What are the actual column names and types?
    – Mike Smith - MCT
    Apr 24, 2018 at 19:22
  • There are 2 options and 4 columns to choose from there are "entered by" and "proofed by" (a person's name, text) and "entered hr" and "proofed hr" (2 decimal number)
    – aaanjah
    Apr 25, 2018 at 20:45
0

You need to use ISBLANK, otherwise you'll get tangled up in IF([Field] = '',x,y) and that will make your statement that much longer

=IF(ISBLANK(OR([Column1],[Column2])),false,true)

should work. Also, <> does means not equal to.

Improve this answer
3
  • This just returned all "completed" cells as "yes"...
    – aaanjah
    Apr 23, 2018 at 22:11
  • Do you have any other suggestions?
    – aaanjah
    Apr 23, 2018 at 23:54
  • Mikes answer below is correct - I'd take his test2 as the answer.
    – Robin in Or
    Apr 24, 2018 at 14:09

Your Answer

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

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