for SALES AND STOCK SHEETS, PLS HELP!!!!!

rahim

New Member
Joined
Nov 28, 2005
Messages
2
hi iam having 2 WORKsheets
one is stock
another is sales

In stock worksheet
iam having like this

Product Name stock-in-hand

p1 20
P3 30
P2 10

and in the sales sheet
iam going to entere the product name and qty
it has to deduct from stock sheet with corresponding stock-in-hand
but if the user delete or modify the sheet(sales) it has to reflect in the stock sheet.

PLS HELP ME, HOW SHOULD I DO IT..........
THANKS
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Brianyo

Board Regular
Joined
Feb 21, 2005
Messages
91
Hi,

I assume.......

1) The Names in the Stock sheet are unique, ie no duplicate records.
2) The names in the Sales sheet will be exactly the same s as they appear in the stock sheet.

If both of these are true you should use sumif(range,ref,value) in the stock sheet.

Where range is the list of product names on the sales sheet, ref is the name on the stock sheet and value is the lsit of sales quatities on the sales sheet.



So on your stock sheet you would have 3 columns

Name of product : stock qty : stock qty less sales qty.
cell A1 Cell B1 Cell C1
p1 20 =b1- sumif(as above)



Brian
 

rahim

New Member
Joined
Nov 28, 2005
Messages
2
i need to add productname

hi brian, thanks a lot it is working fine,
but now, the user may enter wrong product code in sales worksheet,

so i decide to use combobox, how to use...
[forms toolbox i used combobox , Properties:format control,how to link it.....]



or
anyother way to restrict the user in sales worksheet not entering wronginfo


thanks a lot
 

Watch MrExcel Video

Forum statistics

Threads
1,118,086
Messages
5,570,136
Members
412,305
Latest member
Mozz
Top