Register FAQ / Rules Forum Spy Search Today's Posts Mark Forums Read
Go Back   MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Reply
 
Thread Tools Search this Thread Display Modes
Old Jan 15, 2013, 01:53 AM   #1
moodiel
macrumors newbie
 
Join Date: Jan 2013
Paste into visible cells only (Excel 2011)

Hi,
In Excel 2011 for Mac, can you please kindly advise how one can paste a set of data copied from a worksheet into a different worksheet which has been filtered? Excel copies the data into simultaneous rows & columns and therefore copies some of the data into the unseen (filtered) rows, which is what I don't want it to do. Thank you.
moodiel is offline   0 Reply With Quote
Old Jan 15, 2013, 03:48 AM   #2
James Craner
macrumors 68000
 
James Craner's Avatar
 
Join Date: Sep 2002
Location: Bristol, UK
Not sure exactly what you are doing, but easiest method is to take the filter off , from the Menu Data>Filter, copy and insert the data - I always select the rows, rather than the cells to avoid the risk of destroying the integrity of the database, my only moving down some of the columns of the destination database. Then turn filtering back on.
__________________
Productivity Orchard Be more productive with your Mac
James Craner is offline   0 Reply With Quote
Old Jan 15, 2013, 04:08 AM   #3
moodiel
Thread Starter
macrumors newbie
 
Join Date: Jan 2013
Dear James,
Thanks for taking the time to respond. I am working on a packaging database and the data is listed according to sequential barcodes (and not according to suppliers). In order to update the information per supplier I am grouping the original data together as one set of data based on the specific supplier. Therefore the filter is set according to the different suppliers. The new updated information is in a separate worksheet and captured per supplier, therefore grouped in sequential rows. When I paste this updated information into the filtered section then the rows that are hidden are changed accordingly and therefore only part of the visible rows are updated. Hope you can make sense of the problem!
moodiel is offline   0 Reply With Quote
Old Jan 15, 2013, 04:39 AM   #4
James Craner
macrumors 68000
 
James Craner's Avatar
 
Join Date: Sep 2002
Location: Bristol, UK
Ok - This is what you need to do.

1 Take the filters off (Data>Filter) so all the data is shown. I presume in this state the list - "Database" is sorted by Barcode.

2. Add the new data at the bottom on the table. This may mean that database is now not sorted by Barcode as the new items may be out of order.

3.Make sure there is no blank cells between the Column headers and the first row of data in the database. So if the Column headers are in Row 1, the first data row should be in row 2.

4. Select the entire table - You can do this by mouse or via the menu - Edit > Go To... Then click special and select the radio button current region - This should select the entire table - I presume you don't have any totals at the bottom on the table. If you do, you need to make sure that there is a blank row between the last data row and the totals.

5. With the entire table selected sort the table by Barcode to do with via the menu Data>Sort. A dialog box will come up, click the blank highlighted area under column to select the Column label for whatever you called the Barcode Column. Then click ok - Your database will now be sorted into the correct (barcode) order.

6. Click on any cell in the table and then turn filters back on - Data>Filter. When you then select a particular supplier by clicking the filter dropdown in the suppliers column label, the data for the supplier will be filtered and shown in barcode order.
__________________
Productivity Orchard Be more productive with your Mac
James Craner is offline   0 Reply With Quote

Reply
MacRumors Forums > Apple Applications > Mac Applications and Mac App Store

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Similar Threads
thread Thread Starter Forum Replies Last Post
Visual Basic Module/Macro works in Windows Excel but not Mac Excel 2011 chrispy1042 Mac Applications and Mac App Store 2 May 4, 2014 07:41 AM
Excel cells that attribute a score if two cells are the same ellisfella Mac Applications and Mac App Store 0 Nov 21, 2013 05:48 AM
Excel selecting multiple cells; too annoying! Dal123 Windows, Linux & Others on the Mac 1 Oct 27, 2013 02:27 PM
Applescript. Copy filtered cells in Excel Palkan Mac Programming 2 Jul 3, 2012 10:34 AM
Creating Pivots in Excel 2011 that can be used in Excel 2003 (Windows) Extreme Red Mac Applications and Mac App Store 2 Jun 5, 2012 09:51 AM

Forum Jump

All times are GMT -5. The time now is 11:39 PM.

Mac Rumors | Mac | iPhone | iPhone Game Reviews | iPhone Apps

Mobile Version | Fixed | Fluid | Fluid HD
Copyright 2002-2013, MacRumors.com, LLC