Need Quick help please

[sohaib]

Hello..
The following pics show two of my sheets one named Stock and the other Order.. What i want to do is when ever i enter a new record in the order sheet.. The product name should be looked up in the Stock form and the quantity entered in the order form should be deducted from the the quantity in the Stock form ...
For example if i enter a new record in the order form with a product name "Mini Tower" and Quantity "5" then the outcome in the stock form should be 26-5=21.. However one more thing i want to make sure is that i should be able to enter Quantity(Stock) purchased in the stock form .. so it shouldn't be like there is a formula in the Quantity cell so when i purchase further stock and add it to the Stock sheet i.e (Quantity in the stock sheet).. the formula goes away..
Please solve my problem i will be really thankful

Excel Workbook
	A	B	C	D	E	F	G	H
2	Part No.	Component	Type	Quantity	Price	Purchase Date	Expiry Date	Out of date
3	Pt001	Std Tower	Case	34	45.00	*	4/10/2009
4	Pt002	Lg Tower	Case	12	60.00	*	2/21/2009	Y
5	Pt003	Mini Tower	Case	26	55.00	*	2/22/2009	Y
6	Pt004	Alien Skin	Case	5	75.00	*	6/6/2009
7	Pt005	Std Desktop	Case	75	35.00	*	6/7/2009
8	Pt006	PC2-5300 - 1GB	RAM	41	40.00	*	6/8/2009
9	Pt007	PC2-7200 - 512MB	RAM	60	35.00	*	6/9/2009
10	Pt008	PC2-7200 - 1GB	RAM	23	45.00	*	10/8/2009
11	Pt009	PC2-5300 - 512MB	RAM	54	35.00	*	10/9/2009
12	Pt010	PC2-4200 - 512MB	RAM	0	20.00	*	10/10/2009
13	Pt011	PC2-4200 - 1GB	RAM	41	25.00	*	10/11/2009
14	Pt012	5400 - 400GB	HDD	20	145.00	*	10/12/2009
15	Pt013	5400 - 160GB	HDD	78	89.00	*	10/13/2009
16	Pt014	5400 - 100GB	HDD	0	78.00	*	5/10/2009
17	Pt015	7200 - 100GB	HDD	19	85.00	*	2/21/2009	Y
18	Pt016	7200 - 160GB	HDD	11	90.00	*	5/12/2009
19	Pt017	7200 - 400GB	HDD	9	125.00	*	5/13/2009
20	Pt018	7200 - 500GB	HDD	32	185.00	*	5/14/2009
Stock

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
2	Order Number:	Retailer Name	Address	Postcode	Telephone Number	Order Date	Product Type	Product	Unit Price	Quantity	Final Price	Paid	Delivered
3	Od12345	New World	ddnah	92234	0092-44235267	5/17/2009	RAM	Ram	$100.00	2	$200.00	N	Y
4	Od53724	Computer World	sdd	12345	00974-4591838	4/2/2009	Case	Lg Tower	$20.00	4	$80.00	Y	Y
5	Od57321	Regents	Dukhan	12345	00974-423152	3/10/2009	CD/RW	Mini Tower	$50.00	7	$350.00	N	N
6	Od54789	Comps	Matar	12345	00974-4591838	7/10/2009	CD	DVD	$20.00	4	$80.00	N	Y
7	Od52135	Al Jareer	Rayyan	15677	00974-4521367	2/5/2009	HDD	7200 - 500GB	$200.00	3	$600.00	Y	Y
Order

 

[Andrew Poulsom]

Have a sheet for purchases and a sheet for sales. Have another sheet for stock and calculate the balance using SUMIF formulas. That way everything is transparent.