NOTE* Please skip until the next line highlighted in bold if you're not interested in seeing how my setup functions. Thank you!
Hello everybody,
I am currently managing a minor inventory consisting of 101 unique goods. Currently my excel sheet looks like this:
To briefly explain the sheet:
Column F = minimum stock
Column G = current stock. I have a conditional formatting filter here that highlights the stock in a red color if it is < or = the value in the corresponding F column
Column H = Add a value to the stock. So if I enter in 5 on H3, G3 will change value to 11 and the number 5 will immediately disappear from H3 allowing me to enter a new number to add to G3
Column I = Subtract a value to the stock. So if I enter in 6 on I3, G3 will change value to 0 and the number 6 will immediately disappear from I3 allowing me to enter a new number to subtract from G3
Column J = This shows me the last time that the stock in column G was updated. It also takes account for if the value in column G was updated by adding/subtracting to it from column H and I, so it's not completely dependent on manual alterations to the numbers in column G.
Finally, the macro is shown to the right, which is given by:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("H:I"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "G")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 9, c.Value, -c.Value)
Cells(c.Row, "J").Value = Now
Cells(c.Row, "J").NumberFormat = "dd-mm-yyyy"
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub
Now, to my actual question:
Currently, I receive my stock at random intervals ranging anywhere from 7-60 days. Whenever this happens I naturally have to update my stock, which works fine. However, I have some trouble with my expiration dates.
Every batch of items I receive has the same expiration date (meaning that if I order 50 items of "Material XYZ 527" all 50 of these similar materials will have the same expiration date (for example 10-12-2020)). However some times I may still have 1 or 2 of these laying around when I receive a new batch, thus meaning I can have the same item in one shelf but with different expiration dates.
I want to somehow keep track of all these expiration dates through a FEFO (first expired, first out) concept, so I can check and see how many items for each material I have expiring on this and that date.
I would love for this to somehow be compatible with my current system.
Does anybody have any experience with this or good ideas? I would truly appreciate any inputs that I can get! If anybody has some nice videos, guides, links or anything that might help, please do not hesitate to drop those in the comments either
Thank you so much for your time everybody! It is truly appreciated
Best regards,
David
Hello everybody,
I am currently managing a minor inventory consisting of 101 unique goods. Currently my excel sheet looks like this:
To briefly explain the sheet:
Column F = minimum stock
Column G = current stock. I have a conditional formatting filter here that highlights the stock in a red color if it is < or = the value in the corresponding F column
Column H = Add a value to the stock. So if I enter in 5 on H3, G3 will change value to 11 and the number 5 will immediately disappear from H3 allowing me to enter a new number to add to G3
Column I = Subtract a value to the stock. So if I enter in 6 on I3, G3 will change value to 0 and the number 6 will immediately disappear from I3 allowing me to enter a new number to subtract from G3
Column J = This shows me the last time that the stock in column G was updated. It also takes account for if the value in column G was updated by adding/subtracting to it from column H and I, so it's not completely dependent on manual alterations to the numbers in column G.
Finally, the macro is shown to the right, which is given by:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("H:I"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
With Cells(c.Row, "G")
If IsNumeric(.Value) Then
.Value = .Value + IIf(c.Column = 9, c.Value, -c.Value)
Cells(c.Row, "J").Value = Now
Cells(c.Row, "J").NumberFormat = "dd-mm-yyyy"
c.ClearContents
End If
End With
Next c
Application.EnableEvents = True
End If
End Sub
Now, to my actual question:
Currently, I receive my stock at random intervals ranging anywhere from 7-60 days. Whenever this happens I naturally have to update my stock, which works fine. However, I have some trouble with my expiration dates.
Every batch of items I receive has the same expiration date (meaning that if I order 50 items of "Material XYZ 527" all 50 of these similar materials will have the same expiration date (for example 10-12-2020)). However some times I may still have 1 or 2 of these laying around when I receive a new batch, thus meaning I can have the same item in one shelf but with different expiration dates.
I want to somehow keep track of all these expiration dates through a FEFO (first expired, first out) concept, so I can check and see how many items for each material I have expiring on this and that date.
I would love for this to somehow be compatible with my current system.
Does anybody have any experience with this or good ideas? I would truly appreciate any inputs that I can get! If anybody has some nice videos, guides, links or anything that might help, please do not hesitate to drop those in the comments either

Thank you so much for your time everybody! It is truly appreciated

Best regards,
David