Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic sum with a variable twist
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!) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic sum with a variable twist
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. -- Martin "Leanne M (Aussie)" wrote: 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!) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
basic sum with a variable twist
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 -- 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" wrote: 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. -- Martin "Leanne M (Aussie)" wrote: 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!) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If but with a twist:( | New Users to Excel | |||
Sum with a twist | Excel Discussion (Misc queries) | |||
Visual Basic - Variable Sheet Names | Excel Discussion (Misc queries) | |||
Twist on the variable rate problem | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions |