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!

 

[wideboydixon]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

@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.

Not if you read post #8 ...

WBD

 

[JLGWhiz]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Not if you read post #8 ...

WBD

My eyesight is really going to the dogs! Good catch and thanks for the mod. JLG

 

[2clacaze]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

WBD - I neglected, in my hurry, to mention column B has a Hazmat rating of A, B or C for each powder. So column E is correct for where my changes go

 

[2clacaze]

Re: Inventory management nightmare - SOMEONE PLEASE HELP!

Works wonderfully. You have accomplished in a night what has given me weeks of nightmares. I figured it had to be a VBA code, but I only know enough to vaguely understand the directives once written. Creating it is a little beyond me yet. Classes this fall. Thanks again for all your work on my behalf.