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

[marcoR2D2]

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 |
+-------------------------------------------------------------------+

 

[Guest]

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

 

[Roger Govier]

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("D315").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]...