Trying to set up an inventory activity log that automatically updates inventory quantities from menu

[angikoneko]

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 inventory sheets (currently I have 4 sheets, as everything is sorted into their own categories) with current quantities, then there will be a main sheet that will act as an activity log. Any time someone wants an item, they'll go to the activity log, select that item from a drop down, then enter how many they want to remove or add. That amount will then be added or subtracted to the quantity listed on the inventory sheets.

I want to keep the inventory records as hands-off as possible to avoid inaccuracies with the counts. Ideally, anyone accessing the file will only have to input info on the activity log and inventory will update automatically.

I've figured out how to create a drop-down menu so someone can pick the supply they want. But I can't figure out how to set up the 'Quantity' cell. I don't want it to display inventory amounts. It should blank. It's just a cell for adding or subtracting. But I want it to detect the item that was selected from the menu, then when someone enters an amount (like -1 or +1), it automatically adds or subtracts that amount from the quantity listed in the inventory for that item.

If possible, it'd be great if the +/- cell on the activity log would change color based on whether you're removing or adding. Or, if the numbers would display as -1 or +1. I'm not fond of the accounting setting that puts parenthesis around numbers.

I was originally thinking of having a 'Remove" and "Add" column that would only subtract or only add the numbers entered in the respective column. But I couldn't figure out how to make that work either.

Any ideas you guys have would be much appreciated. Using Excel 2013. Thank you.

[#2]

@angikoneko 

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.

[#3]

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

[#4]

@angikoneko 

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.

[#5]

@angikoneko 

OK -- here's a healthy start on a working inventory. You now have

1. a single "Inventory" database, made up of the raw data you had in several separate ones.
2. 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
3. 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.