Hi, Please assist me with a formula. I have explained what I want in the file.

A_SIRAT Are you open to a different approach? I think, to be more specific, you'd be better served by a single transactional table that just begins with a row of opening inventory levels in the two spots. (That's what you have now as a separate table, which I'm suggesting is adding an unnecessary complication.) With a single transactional table, you can handle transfers, incoming shipments, outgoing, and resulting balances, all in one single database.

Yes, I am open to a different approach.Could you kindly edit the workbook and show me with one table.

A_SIRAT Could you kindly edit the workbook and show me with one table.I can do that. First let me ask what rules or assumptions have you built into this? For example:What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa?Are there other entities--other stores?When something is "Issued" what does that mean? Just goes out of your area of concern?You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000.But to which site does that apply? It clearly isn't a limit applied to "Issued" for example.It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else?I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going? Finally, are there any other factors that I've not asked about but that you take for granted?

mathetes Could you kindly edit the workbook and show me with one table.I can do that. First let me ask what rules or assumptions have you built into this? For example:What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa? The Main Store is a controlled internal warehouse that issues items to the Storekeeper (Issuing Store)when they have exhausted or are unable to fully issue items to end users. They can only get the minimum supplied quantity needed to fill their gap. E.g. If chemical XYZ is delivered by the supplier(purchases) in 1ltr packaging and the issuing store have a shortage of 2500ml, then they will get 3ltrs full and be left with 500ml.Are there other entities--other stores? No other except Main store as explained above and the Storekeepers (Issuing Store)When something is "Issued" what does that mean? Just goes out of your area of concern? Yes. Let me add a point. The end users will request for items from the storekeepers. The Storekeeper will check their balances and if it they have enough stock, they will issue. If they have a shortage, then they will request from the Main Store. For example, End users will request for 200ml of chemical XYZ while the storekeepers ((Issuing Store) have 100ml in stock. To cover the shortage of 100ml, they will get a full 1ltr bottle (1000ml) from the Main store which is the minimum quantity supplied by external suppliers(Purchases). They will issue the 200 and be left with 900ml till further request in future.You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000. This one is dictated by the quantities supplied to the Main Store by external suppliers. A supplier could supply his products in 1ltr packaging, or 20ltr packaging. Etc. So when a transfer is called for, then the Main Store will issue what is required in the original packaging quantity. In the above example, the issuing store needed 100ml but because the Main store has 1ltr bottles as the smallest unit, they will issue it in full while the storekeepers will keep their remaining balance. Hope to have clarified.But to which site does that apply? It clearly isn't a limit applied to "Issued" for example. There is no limit to issuance as long as they have enough stock.It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else? It does not apply elsewhere. Mainstore is there as a controlled warehouse and transfers to the storekeepers only when they have shortages.I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going? This is a farm and the requests are either chemicals or fertilizers done on a daily basis. Once released, it goes to be used in their activities. I used one item as an example but they issue tens of items every day. Finally, are there any other factors that I've not asked about but that you take for granted? For me, what is important is to know the running totals of each items per store and the quantity that needs to be requested from the Main store by the storekeepers.I hope to have shed more light.

A_SIRAT I hope to have shed more light.You did for sure. But your responses also raised a few more questions. (In order to have a really robust system/spreadsheet, it is important to spell out all the details.)Your example refers to a Minimum Transfer Size of 1000. I'm thinking now, however, that this limit applies to Item Code C3434/Item Name 30 Col. Are there other Minimum Transfer Sizes for other products? If so, give me a few more examples to work with.Your new set of data refers to quantities as liters and milliliters. So we're not just dealing with abstract numbers; in these cases we're dealing with liquid volumes. That's important to note. Are there other products (as I've asked above) with other types of measurements, e.g., weight, or length? If so, would you spell those out in addition to the examples I've asked for above. [I'm picturing a table here that spells out these details, a table that helps manage the transactions. Your initial example may have been overly simplistic.]In your response to question #5, you include the phrase "This is a farm..." but it's not clear to me whether the "the farm" is the Storekeeper, or a facility IN the farm, or something else. Could you clarify. That's all for now. Thanks.

Formula to add to running totals | Microsoft Community Hub

mathetes
Silver Contributor
Mar 23, 2023
A_SIRAT

Are you open to a different approach?

I think, to be more specific, you'd be better served by a single transactional table that just begins with a row of opening inventory levels in the two spots. (That's what you have now as a separate table, which I'm suggesting is adding an unnecessary complication.)

With a single transactional table, you can handle transfers, incoming shipments, outgoing, and resulting balances, all in one single database.
- A_SIRAT
  Iron Contributor
  Mar 24, 2023
  Yes, I am open to a different approach.
  
  Could you kindly edit the workbook and show me with one table.
  - mathetes
    Silver Contributor
    Mar 24, 2023
    A_SIRAT
    Could you kindly edit the workbook and show me with one table.
    I can do that. First let me ask what rules or assumptions have you built into this? For example:
    What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa?
    Are there other entities--other stores?
    When something is "Issued" what does that mean? Just goes out of your area of concern?
    You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000.
    But to which site does that apply? It clearly isn't a limit applied to "Issued" for example.
    It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else?
    I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going?
    
    Finally, are there any other factors that I've not asked about but that you take for granted?

Forum Discussion

Formula to add to running totals

Share

Resources