1/. First thing I would do is make your data continuous, don't have blank rows seperating your inventories for each location.
This makes analysing your data much easier. (See Sheet "StockTake").
2/. Add a new sheet for the list of unique SKUs and there descriptions, say Sheet "Lookups", this can be hidden
3/. Use Names to define the various ranges dynamically.(See the Names Manager.)
These will automatically adjust as your stocktake changes in size and/or values.
Name:= "SkuList"
Refers to:=
Name:= "Unique_Skus"
Refers to:=
Name:= "Description"
Refers to:=
4/. In Sheet "Lookups" A2 this array formula, confirm with Ctrl+Shift+Enter before dragging down.
This returns all the SKUs found in your stocktake regardless of which location they fall in.
And in B2, to get the SKU description, this standard formula
5/. Use the name "Description" for your Data Validation list in Sheet "Report" A2
And in B2 to get the selected items' SKU
6/. In A5 this CSE array formula, confirm with Ctrl+Shift+Enter before dragging down.
Similarly in B5
Referring to the SKU rather than the description should minimise the possibility of errors due to spelling mistakes.
7/. Finally in B3, dynamically sum B5 down to last QOH found.
This is bit of a ramble, see how you get on with it.
Feel free to ask about any bit you don't understand.
Bookmarks