Ask Your Question

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

asked 2016-08-17 03:21:30 +0200

ricktas gravatar image

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?


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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2016-08-17 19:55:10 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

I based this answer on a technique demonstrated by Lupp as part of his answer in

This is similar to a question answered by @Lupp posted at, but as I already created an example while this site was down then I thought I would still post.

C:\fakepath\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.


edit flag offensive delete link more


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.


ricktas gravatar imagericktas ( 2016-08-19 00:06:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-08-17 03:21:30 +0200

Seen: 1,509 times

Last updated: Aug 17 '16