Forum Discussion
Trying to set up an inventory activity log that automatically updates inventory quantities from menu
You're on the right track. Let me offer
- an observation,
- a couple of suggestions,
- and then a request
The observation: this isn't all that different--conceptually--from the workbook I've created that tracks income and expenses. There are transactions that add cash (coming from different sources, different categories); there are transactions that subtract cash (coming from different sources, different categories).
Suggestion #1: have just a single inventory with all items included. A single database. Use columns (rather than creating separate sheets) to recognize what category a specific items falls into. This will enable you to seamlessly add new items/new categories without redesigning the whole database.
Suggestion #2: continue with your idea of a single sheet in which users enter the item and their action. For clarity with users, I'd recommend the idea that you said you couldn't make work--have a cell with a choice given to "Add item(s)" or "Withdraw" and an adjoining cell for quantity. Making it work is easy with a conditional formula (IF this THEN that ELSE that2)
The request: if you could post a copy of the workbook as you have it now, it would be a lot easier to make more specific suggestions/recommendations. And I'm sure there will be other tweaks that can be suggested and made. Just make sure there's no confidential or private info that shouldn't be made public. If necessary, create a replica with false data that still replicates the functionality you envision.
- 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.
You wrote: "I'm not familiar with IF this THEN that ELSE formulas,"
which I find hard (impossible, actually) to believe, given the level of sophistication of the functions and formulas that are in your spreadsheet. You may not have seen it written out that way, but that is the nature of the very basic IF function. Here's a clip from https://exceljet.net/excel-functions/excel-if-function
Syntax=IF (logical_test, [value_if_true], [value_if_false])In programming languages like BASIC, the THEN and ELSE might be spelled out more; in Excel they just follow a comma or other delimiter within the function.That said, I'll have to study your workbook more later on .... but with this explanation of IF, perhaps you can do more on your own.
