CODEWITHSUNDEEP
openoffice-calc
John D
John D

Reputation: 685

How do I return <empty> from OO-CALC IF-statement?

Using OpenOffice CALC v4.1.3. The dataset contains 400,000 rows and I am looking for the rows that are not in sequence order in column B. Column B contains integers from 1,2,3, etc. to the last row of data.

I am trying to set the cells in column-A with the formula as follow: 

=IF(B3 = (B2+1);[empty];"BAD SEQUENCE")

I do not want to have the TRUE part to be "" (empty-string). I want it to be [empty] or [blank] or [null] or [no-value] or [nothing] (using other language words here) because I want to be able to use the [shift]+[down-arrow] key combination to find the next BAD-SEQUENCE row(s).

When the set of cells is actually [empty] then the [shift]+[down-arrow] navigates to the next "cell-with-value" (if not [empty]).

In this question, I have presented the code to show [empty] but I need the proper OO-CALC representation of [empty] to have empty cells when the if-statement is TRUE.

Your comments and solutions are welcome...thanks John

Upvotes: 0

Views: 334

Answers (1)

Jim K
Jim K

Reputation: 13790

From https://superuser.com/questions/346873/openoffice-calc-how-to-insert-blank-in-a-formula:

No value will make isblank return true, because C1 will always contain a formula, and isblank literally tests for blanks. Not empty strings, but actual empty cells.

Like ISBLANK, Ctrl+Down considers any formula to be non-empty, regardless of its result.

Instead, do the following workaround:

  • Use "" as the [empty] value.
  • Copy column A.
  • Select an unused column such as column C.
  • Paste Special, with the Formulas box unchecked.

Alternatively, instead of using formulas, fill column A with values using a macro.

Upvotes: 1

Related Questions