FIFO Inventory

Not exactly. I want to use the "First In First Out" or FIFO philosophy here, I want to use all of the eggs from the first PO# before going into the stock of the second PO#. But thank you for the help.

Thanks Xx7, it looks like the Pacific Northwest is on point today. That works very well for the conditional formatting, but I am still having trouble with the formulae for the third table. Any ideas?

I put it in the macro instead of a formula.. this should do it.

Sub Button1_Click()

Dim c1 As Range
Dim r1 As Range
Set c1 = Range("K27")       'SO#s total for product...
Set r1 = Range("C4:C26")    'PO#s for product...

Dim i As Integer
For i = 1 To 5
    Call MakeGray(c1, r1)
    Set c1 = c1.Offset(0, 1)    'Move to next column...
    Set r1 = r1.Offset(0, 1)
Next i
End Sub

Function MakeGray(total As Range, rng As Range)

    Dim cell As Range
    Dim s1 As Integer
    s1 = 0
    
    Dim k1 As Integer
    k = 0
    Dim cCol As Integer
    Dim cRow As Integer
    Dim s2 As Double
    Dim s3 As Double
    
    
    For Each cell In rng
        cell.Interior.Pattern = xlNone  'Set blank initially...
        k = s1
        s1 = s1 + cell.Value
        If s1 <= total Then     'If PO qty <= than SO..
            If Not cell.Value = "" Then
                cell.Interior.Color = 14540253  'Color gray...
            End If
        Else
            cRow = cell.Row - 1
            cCol = cell.Offset(0, 16).Column
            'Sum of remaining column...
            s2 = Application.Sum(Range(Cells(4, cCol), Cells(cell.Row - 1, cCol)))
            'Sum of current column...
            s3 = Application.Sum(Range(Cells(4, cell.Column), Cells(cell.Row, cell.Column)))
            If s2 = 0 Then  'If PO not filled... then include remaining qty...
                cell.Offset(0, 16).Value = s3 - total.Value
            Else
                cell.Offset(0, 16).Value = cell.Value
            End If
        End If
    Next cell

End Function

Sorry about that Marvin. I uploaded two different books both called FIFO The "Un"attached file is I completed the math manually. Sorry about that.

On another note, Is there a way to use the =MOD() function to do subtraction instead of division?

Originally Posted by Xx7

I put it in the macro instead of a formula.. this should do it.

Thanks Xx7, that does the trick. I greatly appreciate it.

Originally Posted by MarvinP

Hi Jake or Bill,

It is great to ask a question and get an answer that doesn't relate (anywhere close) to what was asked.

My answer is certainly not!! The Mid Function can't be used in these kinds of problems.
It is very bad also to put all your old eggs in the same basket.
I hope this makes perfect sense based on your question above.

Also Please see the (non) attached file for a perfect explanation of what I'm getting at.

I'm sorry that my message was confusingly written, I work on a production floor with many distractions away from my desk that cause me to lose my train of thought and garble my messages sometimes on forums.

But I was referring to at the beginning and end of my first post there are links to the sheet that I finished manually editing the data. The file at the bottom of my first post is inside of a box that says "Attached Files". That is the file I assume you were referring to. Also I said =MOD() not =MID().

Hello Guys,

I have a requirement for the FIFO Inventory system.

I update the Purchase in one tab and sale in other tab and in the sales tab i want to see me purchase price based on the FIFO method.

Can any one Help me.