basic sum with a variable twist

[Leanne M (Aussie)]

Hi,

I do not know where to start here so I will try to explain what I want to do.

I have a cell - Total Qty
- Remaining Qty

I also have a list which is updated each time stock is taken to show the
following
Product Date Amount
32203 21/10/2008 30
17747 25/10/2008 10
32203 30/10/2008 15
P14567 05/11/2008 50
32203 12/11/2008 20
As you can see the product and amount are variables and the list goes on
each time it is updated with info

What I want is to populate and update the Remaining Qty cell to be the Total
Qty cell minus any qty on the above list.

Therefore each time stock is taken we have an accurate read of the remaining
qty.

Hope someone can help here.

Thanks
Leanne


Leanne M (Aussie)
(Changed Display name from Leanne)
United Kingdom
(Don''t ask me what an Aussie is doing living in the UK!)

 

[Martin Panter]

Is the table reflected 'Goods-In' or 'Goods-Out' and what is the opening
'balance' IE of 32203.

If these are goods out, a simple - but very manual - way to do it would be
by Filter on part number which could autosum just the part number identified.
You would then need to take this number and manually deduct it from the
opening balance. DO NOT AUTOLINK THE RESULT as it will not return the right
answer for any part number.

Outside of this, I think you are looking at a MACRO that will read and sum
the entire sheet, creating separate sum iterationions for each part number.
This MACRO could then be run in real time each time there is another entry to
the table or as a batch process at the end of each day. Could be very time
consuming depending on how big the S/S is.

 

[Don Guillett]

If? I understand, for each in a list in range(a2:a??) with the starting
amount in col b

=b2-sumif(ab2:ab22,a2,ac2:ac22)

 

[Leanne M (Aussie)]

Hi Martin,

The list as yet is not lable but it would be goods out. The opening balance
of each product varies and would be the contents of Total Qty.

Given that the current sheet is very manual, I would like to create a macro
of some kind. By the sounds of it your macro suggestion sounds feasable.
the s/s is not large in s/s terms - ie the list would only be say 100 lines
long at the end of it all.

This is my raw data list (on sheet called Raw Data)

Material Index PkSz BaseUOM SO TQty RemQty
A2024-5G A2024 5 G 8004559451 6 6
A8126-100G A8126 100 G 8004559451 16 14

I then have a list on sheet called History which is derived by entries on
another sheet called Menu (I can provide this macro/code if needed)

The information on History looks like

Product Date Amount
32203 21/10/2008 30
17747 25/10/2008 10
32203 30/10/2008 15
P14567 05/11/2008 50
32203 12/11/2008 20

I am unsure how to 'creating separate sum iterationions for each part number'

Thanks
Leanne