Copying selected data from one sheet to another

Discuss the spreadsheet application

Copying selected data from one sheet to another

Postby diddleyman » Thu Jun 10, 2010 11:48 pm

Hi Folks:

Although I know there are some people who frown on using Calc for non-numeric data, I do this, much the way everyone uses Excel for stuff like this. I have a list of audio books, where they're located, reader etc (5 colums with headings) and would like to copy a subset of this data ( breaking the books down into mysteries, non-fiction etc) to other sheets in the same file. This way I would have a master sheet and a number of additional sheets broken down by category. I know I can do it one by one, but that is WAY too tedious! Copying the whole sheet and then deleting data that doesn't belong in the subset would also be a pain. Is there any way to select the rows you want to copy, then paste them into the other sheet? ( assuming of course that you have copied the same headings into the second sheet) Using Ctrl and selecting all the rows doesn't work. Any help appreciated
Open Office 3.10, Vista
diddleyman
 
Posts: 1
Joined: Thu Jun 10, 2010 11:36 pm

Re: Copying selected data from one sheet to another

Postby thomasjk » Fri Jun 11, 2010 2:10 am

Of course. Select the rows you wish to copy and use cut and paste. Click at the left end of the rows you want to copy and select Cut. Then go to the other sheet and select paste.
Tom K.
Windows 7 Home Premium
LibreOffice 4.2.3.3
thomasjk
Moderator
 
Posts: 3600
Joined: Tue Dec 25, 2007 4:52 pm
Location: North Carolina

Re: Copying selected data from one sheet to another

Postby MrProgrammer » Fri Jun 11, 2010 4:02 am

You have the correct layout for Advanced Filter (heading at the tops of the columns). Let's say your data is in sheet 1 columns A through E and the column you want to select on is headed by "Category". We'll put the mysteries in sheet 2 and the non-fiction in sheet 3. You will need to (temporarily) build your selection criteria in some other cells, say G1 and G2. Put "Category" in G1 and the category you want ("Mystery") in G2. Select the cells in the first five coumns which contain your data (not just the mysteries, but everything). Data, Filter, Advanced Filter, More. You should see the area you selected given as the Data Range at the bottom of the dialog box. Set "Read filter criteria from" to $Sheet1.$G$1:$G$2. Check "Copy results to" and set it to $Sheet2.$A$1. Uncheck "Keep filter criteria" (if set). When you click OK, all the mysteries should be copied to sheet 2. Set cell G2 to "Non-fiction", reselect the columns containing your data, and repeat the filter, but this time set "Copy results to" to $Sheet3.$A$1.

Having said all that, I would encourage you to leave your data all together and use Data, Filter, Auto Filter, or Data, Filter, Standard Filter to select subsets of interest. If you break your data apart by category and then want to find all the books by Tolkien you will have to look in multiple sheets. But with the data as it is now, you can simply use the dropdown for Author and select Tolkien (is using Auto Filter) or request Author = Tolkien (if using Standard Filter). My experience is that if you subset your data, you will regret it later and wind up merging it back together.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1741
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 17 guests