OO Calc: Detecting Blank Cells and Replacing with Cell above?

0

So annoyed I can't find a simple answer to this simple question!

I'm using OpenOffice Calc. I want a formula which will detect if the cell is blank, and if it is to replace it with the contents in the cell above it.

I guess the best way to do this is create a new blank column, put formulae in there. Then copy the formula results and paste into original column as plain text.

Column A        : Column B (formula column)

Joe               Joe
Bloggs            Bloggs
Mack              Mack
                  Mack
                  Mack
Peter             Peter
John              John
                  John
Joe               Joe

Something like this:

=IF(A2="",A1) or =IF(ISBLANK(A2);A1) 

Can't seem to get formulae to work and I only get an error (err:508 / err:509).

Patrick Keane

Posted 2014-09-18T09:22:39.327

Reputation: 101

Have you thought about a macro? – BrianA – 2014-09-18T10:00:43.667

I'm not really that knowledgeable with Macros :( Although I don't see why the above wouldn't work just as good... – Patrick Keane – 2014-09-18T10:28:03.097

Me either but I know they can do repetitive tasks within a range so sound like your answer. We await the experts:) – BrianA – 2014-09-18T10:31:31.090

Verify that OO Calc allows you to leave off the last argument. Either =IF(A2-"",A1,A2) or =IF(ISBLANK(A2),A1,A2) should work. One other thing to check: just learned that OO Calc does some unique things with formula delimiters (";" instead of "," in some cases). Verify that in your formulas. – fixer1234 – 2014-12-24T04:35:10.630

Answers

0

You can use VLOOKUP with a floating range to retrieve the last text value in a column.

        VLOOKUP last text value

The formula in B1 is,

=VLOOKUP("zzzz"; A$1:A1; 1)

Fill down as necessary.

Using VLOOKUP for an approximate match usually requires that the data in the lookup column be sorted. That is not the case here as we are using zzzz as the lookup value to catch the last available text value in column A from the current position. The current position is constructed by locking the row of the first portion of the A$1:A1 and allowing the latter portion to 'float'. This produces A$1:A1, A$1:A2, A$1:A3, etc as it is filled down.

Jeeped

Posted 2014-09-18T09:22:39.327

Reputation: 2 435

Asked: 2014-09-18T09:22:39.327

Viewed: 347 times

Active: 2014-12-24T03:57:33.537