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. Commented 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. Commented Apr 24, 2018 at 18:18
  • What are the actual column names and types? Commented 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) Commented 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"... Commented Apr 23, 2018 at 22:11
  • Do you have any other suggestions? Commented Apr 23, 2018 at 23:54
  • Mikes answer below is correct - I'd take his test2 as the answer. Commented Apr 24, 2018 at 14:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.