OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Copy/paste & add to existing values..Can anyone help ple

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
daz22yrs@yahoo.co.uk
Newbie
Newbie


Joined: 17 Dec 2011
Posts: 3

PostPosted: Sat Dec 17, 2011 6:09 pm    Post subject: Copy/paste & add to existing values..Can anyone help ple Reply with quote

Hi,

I currently make and sell custom designed t-shirts for a living through ebay and have been using CALC to manage stock levels. But keeping stock under control is becoming very time consuming.

I have managed to fine tune the system so that it works perfectly now, but there is just one thing I cannot figure out.

I have created 2 sheets, one which contains all order details, and a second that uses the information in the first sheet to both keep track of which items we have in stock and which items we need to order. The problem is, each time a new stock order is placed, I have to manally add the newly ordered stock to the already existing stock figures.

I would like to be able to copy a 20 cell by 20 cell area of the sheet (stock needed) and paste it on top of an identical 20 cell by 20 cell area on the sheet (current stock level) but so that the figures are added to the already existing numbers, instead of replacing them... Is this possible?...

This way, the "Current stock level" will be increased to the required level and the "Stock needed" section will be automatically zeroed out again.

I would really appreciate any help or advice you can offer me Smile

Many thanks,

Darren.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sat Dec 17, 2011 7:07 pm    Post subject: Reply with quote

On the "Paste Special" dialogue there is a list of operations that can be used.
One of these operations is "Add".
As an example of its use, say Sheet1.A1:B1={1;2) and Sheet2.A1:B1={4;8}.
Copy Sheet1.A1:B1 then select Sheet2.A1, then going Edit|Paste Special... or Ctrl+Shift+V to bring up the "Paste Special" dialogue.
Click the "Add" function on the dialogue then click OK.
Sheet2.A1:B1 will change to {1+4;2+8} = {5;10}
Be careful though, the "Add" operation will remain turned ON and you might not want it turned ON next time you do a Paste Special. It is very easy to fail to notice the operation is turned ON.
After you have completed a session using any Paste Special operation I recommend you copy a blank cell, bring up the Paste Special dialogue, then select "None" under the "Operations" heading, then click OK.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
daz22yrs@yahoo.co.uk
Newbie
Newbie


Joined: 17 Dec 2011
Posts: 3

PostPosted: Sat Dec 17, 2011 8:21 pm    Post subject: Reply with quote

Hi Ken,

Thanks for you reply Smile

Unfortunately I have hit another snag... The "stock needed" section is made to show only positive numbers in red (Highlighting what stock needs to be ordered). All negative numbers are hidden.

When I try to paste special, it is also pasting the negative numbers, which is messing up the rest of the item quantities....

Is there a way to not only hide negative numbers with formatting, but to actually stop them being present in the cell? (whilst still factoring in the sum, eg. If the number in that cell is - 2 , it will not be copied but will not be zeroed out either).

Sorry if this sounds like a real newbie question.. Smile

This is the last hurdle and I can't seem to get over it Sad

Thanks in advance,

Darren.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Dec 18, 2011 5:10 am    Post subject: Reply with quote

Hi Darren,
One way would be to use a separate block of cells with formulae to convert all negative values to zero.
If you copy the separate block of formula cells instead of the cells where some values are negative you can then use Paste Special with the Add operation and Numbers both the only selected options.
See the attached doc where...
A1:B1 = {4;3}
D1:E1 = {-1;2}
D3 has =MAX(D1;0)
E3 has =MAX(E1;0)
making D3:E3 = {0;2}

If you copy D3:E3 then select A1 then use Paste Special (Add and Numbers ticked only, as per picture), A1:B1 will change to {4;5}
http://www.mediafire.com/view/?2dd3qb2dzgmcpdu
Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).


Last edited by ken johnson on Thu Sep 27, 2012 3:35 am; edited 1 time in total
Back to top
View user's profile Send private message
daz22yrs@yahoo.co.uk
Newbie
Newbie


Joined: 17 Dec 2011
Posts: 3

PostPosted: Sun Dec 18, 2011 5:33 am    Post subject: Reply with quote

Hi Ken,

I had just finished doing exactly that when I saw your comment... and it works great! Thank you.

I used =IF(A1<0;0;A1) to create a new table with only positive numbers and recorded a macro to copy> Paste special over the top (with add selected) and assigned a hot key, so now the stock replenishes itself correctly in no time at all.

Thank you very much for putting me on the right track. I really appreciate all of your help.

Many thanks,

Darren.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group