Simple Inventory Formula

Christina C

Help, maybe I am overthinking this or going about it all wrong. I'm trying to create a SIMPLE inventory tracker, it does not include costs. I have a request form where users request an item and I want to subtract it from the existing stock on hand, be able to replenish inventory and adjust inventory if someone audits the closet and finds they have more or less of something and then reflect the "adjusted" or "final" inventory # that I can pull into my dashboard or report.

In the screenshot below, my starting Current stock was "0" for the Mouse item, I "replensished" the mouse stock on another sheet that is linked into this sheet "5" from the replenished sheet. the Items Taken amount of "20" comes in from my tracker sheet which totals the item taken to date (I don't get into checked in and checked out, person I'm doing this for didn't need that).

I expect to see the Adjusted Stock to be "5" for Mouse as I started with 0 and replensished with 5. Every variation of formula and reference eithere gives me circular logic or the wrong amount. I guess I don't know how to keep the Current stock static and make the adjustments with the replenished and Items taken amounts?

I need to keep the current stock static and do the calculation for the "adjusted/new" stock. Then write that amount back to current stock for the next go around. Maybe I can't do this in one sheet and/or need a helper to "do the recalc". I'm getting myself more cofused each time.

Thanks in advance.

Paul Newcome

How are you populating the Items Taken column? The math is adding up so far. If you start with zero, remove twenty, then replenish with 5, then you have -15.

Christina C

Hi Paul,

I have it coming in from a sheet that is totaling it from the intake tracker:

You're right, the math works out on the surface but let's say for the Keyboard line below, 4 are requested and "taken" then the total in current stock becomes zero. Current Stock = Replenished (10) - Taken (now 10) = 0.

Before Taken:

After 4 more Keyboards are taken:

But when I replenish my keyboards and add 5 in I now have -5 when by stock should be 5.

I think I tried to simplify this too much and bringing in the rolling total of "Items" taken is messing me up and I need to do my caluculation once they are taken? I'm in a circular logic mind set now.

Paul Newcome

How are you populating the [Replenished Amount] column?

Christina C

"User" logs replenished amount in another sheet that feeds into the inventory sheet to do the calculation. Replenished amount stays the same until it is overwritten.

Christina C

@Paul Newcome Paul, Just want to let you know that I fixed it. I just mimicked the Items taken and created Items replenished to get a sum and then my inventory ties out. Still testing but it looks good. That's what I get for trying to simplifying it too much and doing all of my work in the one sheet. Thanks for your help.