Hello All

I'm trying to create a stock system using excel 2003, but i'm not to
sure where to start. What i have is some cells with products ,
quantity and parts, what i want to do is to minus 1 product which will
minus 1 out of each part used to make the product... if that makes
sence, i've tried all sorts of sums, but nothing seems to work.
can anyone give me an idea where to start....


--
computime
------------------------------------------------------------------------
computime's Profile: http://www.excelforum.com/member.php...o&userid=31666
View this thread: http://www.excelforum.com/showthread...hreadid=513563

Is this your approach?

product____QtyPod___Part________QtyPart

123456____65_______abcd1_______14
123456_____________fghww2______40
123456_____________lljjhh77______17

If you deduct 1 from the product quantity you want the above to look
like this?

product____QtyPod___Part________QtyPart

123456____64_______abcd1_______13
123456_____________fghww2______39
123456_____________lljjhh77______16


Or do you want something like this?

Product stock
product_____Qty

123456_____65
123457_____112
123458_____17

Parts stock
Part________Qty
abc11______17
abdf44______45
xdfg88______60

Product Structure
Product____part______usage
123456____abc11____1
123456____abdf44____2
123456____xdfg88____1

If you consume quantity n of your product you want to decrease product
stock by n, parts stock for every part used in the structure by n x the
usage of that part?

Hans

yes its the top 1, i need to do...

like: product_____Quantity______parts_____Quantity
12345 10 12345 10
ndkjnd 10
adada 10

and to: product______Quantity______parts______Quantity
12345 9 12345 9
ndkjnd 9
adada 9

God knows how you configure it....


--
computime
------------------------------------------------------------------------
computime's Profile: http://www.excelforum.com/member.php...o&userid=31666
View this thread: http://www.excelforum.com/showthread...hreadid=513563

Okay.

How do you want to specify your "consumption" of 1 product?

1. You enter the product in a cell and the comsumption in another, hit
an "update" button and a macro does the stock correction.

2. You have a "log" of all your consumptions and a stock figure for
every product i.e. the actual stock is at all times inventory stock
minus all comsumptions in the log (for each product of course).

Remember:

A stock system always starts with some kind of inventory (albeit 0) and
then you keep entering your additions or consumptions which are
deducted from the stock. And at some stage you do another inventory and
correct your stock by means of a "correction consumption/addition".

I guess you would want to know at all times what you have consumed and
for what?

And another issue is that you would probably want to have your own,
separate stock of parts maintained along the same lines?

So, maybe the second approach in my first response would be the better
option?

Before you start "coding" it's always good to know what you are trying
to achieve.

So, if this is about your idea of "stock system" we could start
building a template.

Hans

Hello Hans

Each time i remove a product, i want it to remove 1 part from each of
the parts that make the product, the product amount does'nt matter,
that can be anything. Just want to know what parts i have in stock.

Darren.


--
computime
------------------------------------------------------------------------
computime's Profile: http://www.excelforum.com/member.php...o&userid=31666
View this thread: http://www.excelforum.com/showthread...hreadid=513563

okay. To start the process, here's a simple proposal.

inv. actual
Product part stock Consumption stock
123456 123 14 <-- 8 6
123456 234 20 8 12
123456 345 15 <== 8 7
234567 123 30 <-- 7 23
234567 222 17 7 10
234567 345 10 <== 7 3

Consumption log
date product qty
01.02.2006 123456 1
03.02.2006 123456 2
04.02.2006 234567 1
07.02.2006 234567 1
08.02.2006 123456 4
10.02.2006 123456 1
11.02.2006 234567 2
13.02.2006 234567 3
insert new consumption before this line

The formula that updates the stock is in F3: =C3-E3

The formula that calculates the consumption is in E2:
=SUMIF($B$12:$B$20;A3;$C$12:$C$20)

Of course you need to adapt the locations and ranges.

If this is not how you would like to do it, let us know how would like
it to do i.e. in what steps.

Hans