select products to order->in other sheet total num of that product reduced

Hi,

I'm excel beginner,and I'm facing a problem I cannot solve.
I want to enter some values(IDs,numbers) in rows of one sheet,and (immediately or on button click) the corresponding row(by ID) in the other sheet to be modified.How can i do that?

More detailed explanation
I attached a very small .xls example of what I'm trying to do:
- I have two worksheets,one is a simple product list,other a simple order list
- Problem: in the order sheet i select product ID (from a list),and number of products i order; in products sheet total number of that product should be reduced for the number of products that are ordered.
(for example,if i had 100 shirts in products sheet,when 10 shirts are ordered in order sheet,90 should be left in products sheet).

One more thing,in the order sheet i can order hats first,then shirts,then sneakers,so random order-i dont know what is ordered in which row.

I guess I need some macro to do that...

Thanx in advance

see your post on the general forum
paul
remove nospam for email addy!



"marcoR2D2" wrote:

>
> Hi,
>
> I'm excel beginner,and I'm facing a problem I cannot solve.
> I want to enter some values(IDs,numbers) in rows of one sheet,and
> (immediately or on button click) the corresponding row(by ID) in the
> other sheet to be modified.How can i do that?
>
> _More_detailed_explanation_
> I attached a very small .xls example of what I'm trying to do:
> - I have two worksheets,one is a simple product list,other a simple
> order list
> - Problem: in the order sheet i select *product ID* (from a list),and
> number of products i order; in products sheet total number of that
> product should be reduced for the number of products that are ordered.
> (for example,if i had 100 shirts in products sheet,when 10 shirts are
> ordered in order sheet,90 should be left in products sheet).
>
> One more thing,in the order sheet i can order hats first,then
> shirts,then sneakers,so random order-i dont know what is ordered in
> which row.
>
> I guess I need some macro to do that...
>
> Thanx in advance
>
>
> +-------------------------------------------------------------------+
> |Filename: Simple2sheets.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4432 |
> +-------------------------------------------------------------------+
>
> --
> marcoR2D2
> ------------------------------------------------------------------------
> marcoR2D2's Profile: http://www.excelforum.com/member.php...o&userid=32327
> View this thread: http://www.excelforum.com/showthread...hreadid=520879
>
>

Hi Marco

You could enter the following formula in cell C3 of Sheets All Products
=IF(ISERROR(MATCH(A3,Order!$A$3:$A$15,0)),0,VLOOKUP(A3,Order!$A$3:$B$15,2,0))
and in cell D3
=B3-C 3
Copy formulae down for the length of your product list

Attach the following macro to your button on Orders to update the
figures in All Products and clear down the entries on the Order sheet

Sub UpdateProducts()
Sheets("All Products").Range("D3:D15").Copy
Sheets("All Products").Range("B3").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Order").Range("B3:B15").ClearContents
End Sub

Adjust the ranges in the formulae and in the macro to deal with any
changes in the size of your ranges.
--
Regards

Roger Govier


"marcoR2D2" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi,
>
> I'm excel beginner,and I'm facing a problem I cannot solve.
> I want to enter some values(IDs,numbers) in rows of one sheet,and
> (immediately or on button click) the corresponding row(by ID) in the
> other sheet to be modified.How can i do that?
>
> _More_detailed_explanation_
> I attached a very small .xls example of what I'm trying to do:
> - I have two worksheets,one is a simple product list,other a simple
> order list
> - Problem: in the order sheet i select *product ID* (from a list),and
> number of products i order; in products sheet total number of that
> product should be reduced for the number of products that are ordered.
> (for example,if i had 100 shirts in products sheet,when 10 shirts are
> ordered in order sheet,90 should be left in products sheet).
>
> One more thing,in the order sheet i can order hats first,then
> shirts,then sneakers,so random order-i dont know what is ordered in
> which row.
>
> I guess I need some macro to do that...
>
> Thanx in advance
>
>
> +-------------------------------------------------------------------+
> |Filename: Simple2sheets.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=4432 |
> +-------------------------------------------------------------------+
>
> --
> marcoR2D2
> ------------------------------------------------------------------------
> marcoR2D2's Profile:
> http://www.excelforum.com/member.php...o&userid=32327
> View this thread:
> http://www.excelforum.com/showthread...hreadid=520879
>