Forum Discussion
Trying to set up an inventory activity log that automatically updates inventory quantities from menu
Edit: I added a demo of the inventory workbook I'm trying to make I'm creating an inventory workbook to keep track of supplies at work. The idea is that the supplies will be listed on their own ...
I've added a demo of the workbook I made. How would I make the coding work? I'm not familiar with IF this THEN that ELSE formulas
OK -- here's a healthy start on a working inventory. You now have
- a single "Inventory" database, made up of the raw data you had in several separate ones.
- a single Activity Log, where I added a set of rows that "initialize" the quantity (based on the numbers you had in your various separate categories). In this activity log there's
- a column for "Activity" with three separate entries. You may want to eliminate the "Initialize" from the set of acceptable answers for most users, but it's there to allow a starting "balance" for each item.
- Other than that, the user can "Remove" or "Replace" items
- Then there's a formula that adds or subtracts based on the action taken
- Then, back in the single Inventory database, the quantity now is dynamically calculated for each item using SUMIF to start with the initialized number, adding or subtracting from that based on replace or remove actions in the Activity Log
I also converted the tables in both cases to official Excel Tables, which makes it possible for new rows to be added and accounted for in all the different relationships.
There are a few comments meant to help you see how to take this to the next level. Feel free to come back with questions. You will need the most recent version of Excel for the new data validation to work (It uses UNIQUE, a new dynamic array function). If that doesn't work, you can create the data validation the same way as you had it before.