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).

4
  • Have you thought about a macro?
    – BrianA
    Sep 18, 2014 at 10:00
  • I'm not really that knowledgeable with Macros :( Although I don't see why the above wouldn't work just as good... Sep 18, 2014 at 10:28
  • Me either but I know they can do repetitive tasks within a range so sound like your answer. We await the experts:)
    – BrianA
    Sep 18, 2014 at 10:31
  • 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
    Dec 24, 2014 at 4:35

1 Answer 1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .