Forum Discussion
Formula Error For Inventory Sheet
Afternoon All,
Wondering if someone can assist.
I am needing what is probably a basic cell formula, but cannot get it to work.
I will try to describe what i want the cells to do in as much detail as possible.
I am wanting to keep track of a small group of stock items, and would like the following to happen :
( cells named for ease )
Cell B5 - Total Quantity of item in location A ( needs to be able to be manually increased as stock comes in )
Cell C5 - Quantity of said item in a location B
Cell D5 - Quantity of said item in location C
When items are taken from location A and placed in either location B or C ( which i manually change the quantity in the cell ), i would like the total in cell B5 to automatically lower whilst being able to manually increase B5 when stock replacement arrives. It works untill i manually increase B5, as this then clears my formula.
Hope this makes sense, and thank you.
3 Replies
I am wanting to keep track of a small group of stock items, and would like the following to happen :
( cells named for ease )
Cell B5 - Total Quantity of item in location A ( needs to be able to be manually increased as stock comes in )
Cell C5 - Quantity of said item in a location B
Cell D5 - Quantity of said item in location C
When items are taken from location A and placed in either location B or C ( which i manually change the quantity in the cell ), i would like the total in cell B5 to automatically lower whilst being able to manually increase B5 when stock replacement arrives. It works untill i manually increase B5, as this then clears my formula.
If I can be so bold, I'd encourage you to re-think your design, altogether. A major part of keeping track of items in an inventory is having a clear history of what has happened with those items in inventory. And what you've described does not keep history. This is clear from the several uses of "manually change/increase" in that quoted paragraph.
I've attached a very simple--and I do mean both words; it could be developed with far more details and nuance--example of how a record of history could be created, and still give you an ongoing current count. And note, the history can enable you to go back at any time and discover what has led to the current levels.
Be careful to separate data input cells from calculation cells. If they are mixed it is inevitable that sooner or later you will either forget to update an input cell or will overwrite a formula cell.
A possible way forward is to use a Table to record all stock transfers. The table can total the volume of each transfer.
I have used an array (named 'transfers') to specify what each transfer means in terms of stock held at each location.
The formulas used to calculate the final stock levels given the original quantities and total updates will probable look unfamiliar to you but each should update as you enter data.
Formula1 (any Excel version) = original + MMULT(totalMovements, transfers) Formula2 (Excel 365) = original + BYCOL(TOCOL(totalMovements) * transfers, LAMBDA(x, SUM(x)))giving the resulting quantities
ShaunJM HI,
Given that the stock list was not provided, my suggestion is just based on the "Total Quantity" of Locations A, B and C. It's a basic formula that allows you to enter the number of stock in B8 / B9 / B10 and then B6 would be updated automatically. C6 = C5 + B8 and D6 = D5 + D9.
Hope it can help you, please feel free to provide the more details if it is not fit for you.
