[Solved] Monitoring a Container's Volume/Inventory

[Zeke]

In making hand soap - we use certain amounts of oils & fragrances - it would be helpful to know at any time, by looking at the data, which reagents need to be replenished.
- Input variable would be the amount of a reagent used [U2].
- [V2] would be the initial/current amount of reagent in the reagent container.
- W2 contains a formula to subtract U2 from V2, giving the reagent amount remaining in the container.
- my problem is how do I get the information produced in W2 transferred back into V2 - for the next cycle/use - putting a formula in V2, such as =W2 produces an error [as you know]. Thank you for your help!

 

[Yousuf Shovon]

my problem is how do I get the information produced in W2 transferred back into V2 - for the next cycle/use - putting a formula in V2, such as =W2 produces an error [as you know].

Hello Zeke,

Welcome to ExcelDemy Forum! Thank you for the thorough explanation. Although a demo dataset would help a lot. I understand you wish to monitor a container's volume/Inventory. Here is our suggestion to do so.

Adding or subtracting some increment from a container total is a common, but not commonly advised, method of doing this. Most of the time, we recommend a transaction model for the consumption and replenishment of supplies.

Basically, you have to have some formula that says:

To get 50 gallons of soap, you need x gallons of oil, y gallons of fragrance, z gallons of a colorizing agent, etc. (for which one would hope the total of the ingredients is 50 gallons).

Then you can plug in some computation based on the desired batch size to figure ingredient consumption.

Let me know if you have further queries. Thank you.
Regards,
Yousuf Shovon

 

[Zeke]

Please let me try again - the inventory I wish to keep up with is not in the 50 gallons of soap, but primarily I'd like to keep up with the the fragrance oils, which are very expensive.

If I start with a 16 oz. bottle of fragrance & remove 5 oz. each time I make a batch of soap, then over several days & batches, I'd like to look at the database & know how much fragrance is left in the source bottle.

I can do the formulas [see attached screenshot] to generate the amount of fragrance remaining in the fragrance source bottle [- W2 contains a formula to subtract U2 from V2, giving the reagent amount remaining in the container].

What I'm having a problem with is getting the 14.50 oz. result [W2] back into V2 so I can subtract the next used fragrance amount [ - my problem is how do I get the information produced in W2 transferred back into V2 - for the next cycle/use - putting a formula in V2, such as =W2 produces an error [as you know].

Thank you for your help!

 

[Yousuf Shovon]

Please let me try again - the inventory I wish to keep up with is not in the 50 gallons of soap, but primarily I'd like to keep up with the the fragrance oils, which are very expensive.

If I start with a 16 oz. bottle of fragrance & remove 5 oz. each time I make a batch of soap, then over several days & batches, I'd like to look at the database & know how much fragrance is left in the source bottle.

I can do the formulas [see attached screenshot] to generate the amount of fragrance remaining in the fragrance source bottle [- W2 contains a formula to subtract U2 from V2, giving the reagent amount remaining in the container].

What I'm having a problem with is getting the 14.50 oz. result [W2] back into V2 so I can subtract the next used fragrance amount [ - my problem is how do I get the information produced in W2 transferred back into V2 - for the next cycle/use - putting a formula in V2, such as =W2 produces an error [as you know].

Thank you for your help!

Dear Zeke,

Thanks again for the detailed explanation.
Your simplified Fragrance in a Bottle model seems to lack information about refilling the bottle when it gets low. I can't tell you HOW or WHEN to do that because I can't see what else you are doing. This is still a variant of an inventory problem. It isn't complete unless you have both consumption and replenishment accounted for. The problem with doing this with Excel is that the cells are all independent of each other UNLESS you put a formula in a cell that references another cell. That means a lot of formula repetition.

Regards,
Yousuf Shovon

 

[Zeke]

When the fragrance bottle volume gets low {less than 5.0} - as determined by W2 - W2 contains the formula =SUM(V2-U2) - I replenish the supply by ordering more bottles & adding that amount to V2 --
So, how can I solve the problem?

Thanks for your help

 

[Lutfor Rahman Shimanto]

When the fragrance bottle volume gets low {less than 5.0} - as determined by W2 - W2 contains the formula =SUM(V2-U2) - I replenish the supply by ordering more bottles & adding that amount to V2 --
So, how can I solve the problem?

Thanks for your help

Thank you for explaining your issue with such clarity, Zeke. After exploring this thread, I understand that you want to get the information produced in the W column and transfer back the values in the V column for the next cycle. Specifically, the remaining amount must be the initial amount for every next cycle.

To demonstrate this situation, I have made a sample dataset. To solve the problem you are addressing, I have developed an Excel VBA code. You must paste the code into a module. You can create an event to dynamically run the code if any changes occur in the U column.

Steps:

• Right-click on the sheet name and click on View Code.

​

• Next, copy and paste the below code.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Columns("U")) Is Nothing Then
        Call UpdateReagentAmount
    End If

End Sub

• Now, press Alt+F11 to open the VBE and paste the following code in a module.

Sub UpdateReagentAmount()
   
    Dim ws As Worksheet
    Dim reagentAmount As Double
    Dim usedAmount As Double
    Dim remainingAmount As Double
    Dim inputNumber As Double
    Dim lastRow As Long
   
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(Rows.Count, "U").End(xlUp).Row
   
   
    If lastRow = 2 Then
   
        reagentAmount = ws.Range("V" & lastRow).Value
        usedAmount = ws.Range("U" & lastRow).Value
        remainingAmount = reagentAmount - usedAmount
       
        ws.Range("W" & lastRow).Value = remainingAmount
   
    Else
   
        reagentAmount = ws.Range("W" & lastRow - 1).Value
        ws.Range("V" & lastRow).Value = ws.Range("W" & lastRow - 1).Value
        usedAmount = ws.Range("U" & lastRow).Value
        remainingAmount = reagentAmount - usedAmount
               
        ws.Range("W" & lastRow).Value = remainingAmount
   
    End If
   
   
    If remainingAmount < 5 Then
   
        inputNumber = InputBox("Exceed safety stocks. Add more quantities.")
        ws.Range("W" & lastRow).Value = ws.Range("W" & lastRow).Value + inputNumber
        MsgBox "Bottles are added."
        ThisWorkbook.Save
       
    End If
   
End Sub

How Does the Code Work:

The code updates the amount of a reagent in a worksheet by considering the used amount. It calculates the remaining amount and writes it in the corresponding cell if there is only one row of data. When there are multiple rows of data, it copies the reagent amount from the previous row to the current row and calculates the remaining amount. If the remaining amount falls below 5, the code prompts the user to add more reagents. The user adds the inputted quantity to the remaining amount, and a message informs that bottles have been added. Finally, the Workbook is saved.

OUTPUT: I am linking a VIDEO for you to understand all the events clearly.

VIDEO​

This idea helps you reach your objective. I'm also providing you with the Workbook used to analyze your problem to assist you in understanding it better. Please get in touch with us again if you have any more questions or issues.

Regards
Lutfor Rahman Shimanto