Hi Linda,
I'm copying to the list so that others can comment on or learn from your
experience. First of all, if rows or columns are hidden by using format
/ row / hide, copy and paste will always return all the data, hidden
rows included. Looking at you spreadsheet, I can see that you've done a
lot of work that could have been done by using the function sumif. You
first need a list of just invoice numbers. You can get this by sorting
the first three colmuns on the data page by invoice number, then adding
a formula that will give you only one instance of each. There are
several ways to do this, but the simplest is: (given that the the
invoice number is in column B):
= IF(b1=b2;"";b1)
Fill down with that formula, copy the column, then paste values only
into a new column. Sort that column, then enter this formula beside the
list and fill down:
=SUMIF(B$2:B$211;G1;C$2:C$211)
I've done this in your spreadsheet and will send it back to you. The
first formula is in column E, the 'one-of' list of invoices is in column
G, and the totals in column H. To get column G, I copied column E, used
paste special, strings and numbers only into G, then sorted.
I hope this gives you want you wanted.
tc
lindamurphy wrote:
Hi Anthony
Thank you for responding to my request for help regarding copy & paste
of _Visible Cells Only._
I received a great number of responses, most only read and others who
did not know just like me.
I have followed your information but appear to still not have got it!!!
I therefore have attached a small exercise of what I want to achieve.
I know your time and knowledge is a valued possession, however if you
could take a look and advise me further it would be much appreciated.
Sheet 1 List of data which I sorted by store, invoice no
and $ value.
All other data I deleted.
Sheet 2 I subtotaled by invoice no and $ invoice value
Sheet 3 I clicked onto tab 2 (top left hand corner) to show
only invoice no total and $ value. (All other data having been hidden)
Sheet 4 Is where I would like to copy and paste the visible
cells only into.
Highlighting the data and using the copy paste via clipboard copies
all data including hidden data.
What am I doing wrong?
Should I be using a different method to sort and subtotal?
I have sourced help through the internet and help in Open Office, but
to no avail.
Appreciate any response.
Many thanks
**/Linda Mary Murphy/**
/*/19 Oatberry Crescent/*/
/*/Shailer/*//*/ /*//*/Park/*/
/*/Brisbane/*/
/*/Australia/*//*/ 4128/*/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
