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

  • Have you thought about a macro? – BrianA Sep 18 '14 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... – Patrick Keane Sep 18 '14 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 '14 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 '14 at 4:35

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.

|improve this answer|||||

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.