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)

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.

  • 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 '18 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 '18 at 18:18
  • What are the actual column names and types? – Mike Smith - MCT - MVP Apr 24 '18 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 '18 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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