Forum Discussion
Inventory Sign Out Quantity Issue
=VSTACK(A1:G1,
FILTER(
HSTACK(
A2:B11,
MAP(A2:A11,F2:F11,LAMBDA(barcode,job,SUMIFS(C2:C11,A2:A11,barcode,F2:F11,job))),
D2:D11,
MAP(A2:A11,F2:F11,LAMBDA(barcode,job,SUMIFS(E2:E11,A2:A11,barcode,F2:F11,job))),
F2:G11),
G2:G11<>"")
)
Does this formula return the intended result?
Sadly not, it needs to update on the fly as you are scanning and putting the job number in. I am messing with VBA codes trying to get that to do what I need. From what I can find normal excel formulas can't clear a spot if it does match. That or just make a separate application for the sign out and make it export properly into excel.
You are correct. For a formula to change user input would violate a fundamental principle of Excel (that of immutability) as it would any other functional programming language. For a formula to work you have to create a new copy of the data.
VBA (as an imperative programming language) will allow you to make such changes on the fly. The downside is that you then have no record of the original input so any data entry or processing error will no longer be visible. If you have other data cleaning steps you need to keep track of the order in which they are applied (not an impossible task, but not one Excel is set up to do) because the result will be different.
