CODEWITHSUNDEEP
excelcellsopenoffice-calc
akris
akris

Reputation: 13

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.

Upvotes: 0

Views: 246

Answers (1)

Lyrl
Lyrl

Reputation: 935

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.

Upvotes: 1

Related Questions