Calc: If a field contains data, copy the entire row to another worksheet

[ricktas]

Hi everyone.

First time poster. As a background, I have taken over a position in a new company and they use Libre Office. I have several spreadsheets that could do with some tidying up and I have been working through those. But I am stumped on one.

1. I have a file that has several worksheets.
2. Worksheet 1 has a heap of master data in it
3. There is a field on the master data worksheet that staff complete with a number, each week.
4. But there are a lot of rows that do not need that number inserted
5. At present the previous person in my role would sort the master sheet each week, based on that one field, then cut-n-paste what they needed into the second worksheet.

I was wondering if there is a formula/macro I could use that would automatically copy a row, with say data in a set cell, across to the other worksheet, but append each line to the next line on the new worsheet?

Eg.

Column 1 Column 2 Column 3 Column 4

item Red supplier 4

item Blue supplier

item Green supplier 1

All I want to achieve is : if there is a value in column 4, copy that entire row to the other worksheet. If the column 4 is blank, do not copy to other worksheet. Such that all rows with a value in column 4 are appended to the other worksheet.

Hope someone can assist, and thank you in advance

[mark_t]

I based this answer on a technique demonstrated by Lupp as part of his answer in http://en.libreofficeforum.org/node/13345.

This is similar to a question answered by @Lupp posted at http://en.libreofficeforum.org/node/15639, but as I already created an example while this site was down then I thought I would still post.

Untitled 9.ods

Helper column H picks the next row of sheet1 that is not blank on column 4.

Formula in cells A1 to D13 copies the data from Sheet1 that is selected by row number in column H.

mark_t

[ricktas]

thanks Mark. Sorry for the slow reply as I could not get logged in. Everytime I tried, I was told the site was down. I Appreciate your fast help with this.

Rick