Search code examples
excelcellsopenoffice-calc

how to select data from cell and copy to range until other data found open office calc

There is the issue

enter image description here

I wonder if there is a way like if there is date(or text,e.g.) on B1 cell then copy B1 data to the range A2-A19, until the next cell with data (B20) and so goes on. Because on much data cannot be done by hand.

Solution

  • If the "date" values in column B are actually text, then enter into A1:

    =IF(ISERROR(AND(FIND("/";B1)=3;FIND("/";B1;4)=6));OFFSET(A$1;ROW()-2;0);B1)

    This tests for cell B1 having a "/" character at the third and sixth positions in the string.

    If the "date" values are in column B are actually entered into the spreadsheet as dates, then enter into A1:

    =IF(YEAR(B1)<1950;OFFSET(A$1;ROW()-2;0);B1)

    This tests for the integer value of B1 (in the case of a date, this value is the number of days since December 31, 1899) falling in a year earlier than 1950. You may have to adjust 1950 to a different year depending on the details of your data.

    In either case, if the test fails then the value from the previous row of column A is displayed (offsetting cell A1 by the current row minus two: for example on row 3, A1 will be offset by 3-2= 1 and A1 offset by 1 is A2).

    If the test succeeds then the value from the corresponding row in column B is displayed.

    The $ in the A$1 will keep that value constant when the formula is copy-pasted, so the offset calculation will work correctly. All the other values will adjust appropriately when you copy-paste down the row.