Inventory management

[2clacaze]

I can't get my inventory to work the way I want it to. I work for a powder-coating company, so our inventory is dry, colored powder, tracked in pounds. Right now it looks something like this:

Sherwin Williams BK01 35
BK03 14
BK08 85
Rodda Gloss Black 76
Midnight 12
Cardinal RAL 7011 62
RAL 7112 19
RAL 7003 4


The powder manufacturer is in the first column, with their respective colors ranked below each in the next column. The third column is the current weight, in pounds, on hand. I want to add another column to reflect daily powder usage, in pounds,

Sherwin Williams BK01 35 3
BK03 14
BK08 85 20
Rodda Gloss Black 76 15
Midnight 12
Cardinal RAL 7011 62 8
RAL 7112 19 2
RAL 7003 4

And I'm fine to this point. Not all colors are used daily, and I don't subtract zero. But I want the form to update after I hit enter on each usage cell:

Sherwin Williams BK01 35 3

*ENTER*

Sherwin Williams BK01 32

I run into circular references when trying a =SUM('currentweight'-'usage')

This inventory is a mess! Someone please HELP!

 

[JLGWhiz]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

You would probably need to use VBA with the Worksheet_Change event to do what you want.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
        If Target.Offset(, -1).Value > Target.Value Then
            Target.Offset(, -1) = Target.Offset(, -1).Value - Target.Value
        Else
            MsgBox "Insufficient quantity on hand to complete transaction"
            Exit Sub
        End If
    End If
Application.EnableEvents = True
End Sub

To install this procedure, right click the sheet name tab and then click 'View Code' in the pop up menu. Paste the procedure into the large code pane and close the vb Editor. If not already done, save your workbook as a macro enabled workbook to preserve the code. The code should now execute when you make an entry into column D.

 

[Legacy 167133]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

I would love to help you, but without a sample Workbook, with your Code and Formulas, I am afraid, I am at a lost.

Removed by Moderator - refer #4 of the Forum Rules

 

[igold]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Hi,

I would change the way you have your data set up. I don't know what all the numbers in your data stand for but it should look something like this:

Mfg Color Weight Shewin Williams BK01 35 40 Shewin Williams BK08 85 22 <colgroup><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

and then additional columns that you are using.

Each unique record like a different color should have it's own row. If you lay your data out like this, manipulating it will become much easier.

I hope this helps...

igold

 

[2clacaze]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Tell me how to show a sample workbook. I would give you a full copy if it helped

 

[wideboydixon]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

If you have something like this:

Book1
	A	B	C
1	Sherwin Williams	BK01	35
2		BK03	14
3		BK08	85
4	Rodda Gloss	Black	76
5		Midnight	12
6	Cardinal	RAL 7011	62
7		RAL 7112	19
8		RAL 7003	4
Sheet1

Then the solution in Post #2 above should work for you. The only change you might want to make is to set Target.Value = "" at the end.

WBD

 

[JLGWhiz]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

If you have something like this:

Book1
	A	B	C
1	Sherwin Williams	BK01	35
2		BK03	14
3		BK08	85
4	Rodda Gloss	Black	76
5		Midnight	12
6	Cardinal	RAL 7011	62
7		RAL 7112	19
8		RAL 7003	4
Sheet1

Then the solution in Post #2 above should work for you. The only change you might want to make is to set Target.Value = "" at the end.

WBD

Also, at the bottom of post #2 and this post the last word is Attachments. Click on it.

 

[2clacaze]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

I will try that. Will i have to paste the VBA on each sheet? They are separated by color group. The table above accurately depicts the layout. Column A is the vendor. B is the Hazmat rating, A, B or C. Column C is the specific color by name or code. D is the current weight this morning. E is where i enter the usage for that day, whether a few pounds of a few kinds being used or 50 lb boxes being delivered, or both. F is where i currently have my formulae...=SUM(D4-E4), et al. It leaves me with this morning's start weight and the end of day changes, but doesn't move the values to be ready for tomorrow. Most days it will be 2-20 lbs of a few colors each day and boxes refilling weekly on various days.

 

[wideboydixon]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Yes you'll need to paste the code on each sheet as it's triggered when something on the sheet changes. Make sure you put the code in the worksheet module in VBA. You'll need to change "D:D" to "E:E" from what you're describing here. I'd also remove the formula in column F as it won't make sense any more. So for each sheet you then have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

If Target.Column = 5 Then
    If Target.Offset(, -1).Value > Target.Value Then
        Target.Offset(, -1) = Target.Offset(, -1).Value - Target.Value
        Target.Value = ""
    Else
        MsgBox "Insufficient quantity on hand to complete transaction"
    End If
End If

Application.EnableEvents = True

End Sub

Note the line in red which is optional and clears the value you just entered in to the cell.

WBD

 

[JLGWhiz]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Yes you'll need to paste the code on each sheet as it's triggered when something on the sheet changes. Make sure you put the code in the worksheet module in VBA. You'll need to change "D:D" to "E:E" from what you're describing here. I'd also remove the formula in column F as it won't make sense any more. So for each sheet you then have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

If Target.Column = 5 Then
    If Target.Offset(, -1).Value > Target.Value Then
        Target.Offset(, -1) = Target.Offset(, -1).Value - Target.Value
        Target.Value = ""
    Else
        MsgBox "Insufficient quantity on hand to complete transaction"
    End If
End If

Application.EnableEvents = True

End Sub

Note the line in red which is optional and clears the value you just entered in to the cell.

WBD

@wideboydixon, as I read the OP, column C is the remaining balance and column D would be where the demand quantity is entered. That would make column D the target column.