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