Forum Discussion

TWW20's avatar
TWW20
Copper Contributor
Nov 17, 2020

Reducing a cell value using vlookup

Hello, I have come to a sticking point and hoping someone can help. I have two arrays of numbers. The first is the product barcode and current stock, lets call this the "stock array". The secon...
excel
Formulas and Functions
Craig Hatmaker's avatar
Craig Hatmaker
Iron Contributor
Nov 17, 2020

TWW20 

 

Trust me. We want to preserve our beginning balances. From those we want to subtract issues to obtain our ending balances. We do not want to overwrite beginning balances because that will destroy any chance of fixing errors. Besides, Excel's formulas cannot overwrite literals (hard coded values). VBA can, but not formulas.

 

I also urge everyone to use tables because they are dynamic named ranges that will automatically extend formulas as we add orders or stock items and make formulas self documenting. Below is a picture of how I would do it and attached is the spreadsheet with tables and formulas.

 

BTW - I'm using SUMIFS instead of VLOOKUP because it is very likely that we could have multiple orders for the same SKU. VLOOKUP will only find one entry. Not all.

 

ulas.

Resources