How do I change numbers in cells by the option selected in a drop down box?

[SamLev]

Hi there,
I’ve been trying for ages to get this sorted but I just can’t work out what formula I need.

It’s like a stock control issue.

I have a list of items that I make in my drop down box, once I have selected one of the items it means I have made it using some items in my stock control list. So once an item is selected I want it to automatically reduce my stock by the amount I use to create the product.

So let’s say column B has all the same list of products in the drop down box, in colum F are a list of ingredients, and Column G has the amount of ingredient in stock.

If I select ‘beans’ from the drop down box - as this would mean that I have just manufactured a tin of beans - then I want the stock of the ingredients to reduce by a specific number, so it takes 1lt of water (I have 10 lltr in stock so this would be reduced to 9) 200g of sauce (I have 500g in stock so this would be reduced to 300g) and 10 tins (I have 100 in stock so this would reduce to 90)

I hope I am making sense here, please let me know if this formula can be done.

Thanks

[LKeithJordan]

UPDATE 20150916

When I wrote up the description below, I “took it off the top of my head.” Since then, I decided to actually create a mock-up.

I have attached an odg of my ods that may better illustrate my thoughts. In the mock-up, I also used the =VLOOKUP function, some range names, and a few other techniques. If I were going to work further, I would add conditional formatting to the raw material (RM) stock balances so that they would warn if falling below the Re-order Point, or when they reached or fell below zero.

As you can see, however, this is not just a “formula.” I still maintain you would be better off using a database approach – unless you will only have a few entries (and your description indicates many entries over time).


'===============================================


ORIGINAL ANSWER

Frankly your described need calls for a database application, NOT a spreadsheet application. That said – if you are determined to use a spreadsheet to control your inventory, I would probably consider a scenario similar to the following:

Set up a spreadsheet

Cell A5: “Date”

Cell B5: “Description”

Cell C5: “Txn Type” (Add or Deduct)

Cell D5: “Stock” (FG; Tins; Beans & Sauce; Water – this could be the “drop down box” you mention)

Cell E5: “Qty”

Cell F5: “FG” (Finished Goods Qty)

Cell G5: “Tins” (Qty)

Cell H5: “Beans & Sauce” (Qty)

Cell I5: “Water” (Qty)

The first entry should be:

In Cell A6, enter Current Date

In Cell B6:E9, enter a Description of “Balance Forward” with a Transaction Type of “Add” for each stock item (Enter the current balances of FG and the raw materials)

Cells F2 through I2 should each contain a =SUM formula for sufficient cells in their respective columns to contain transactional activity.

Beginning with Row 6, Cols F:I should contain a formula that calculates a quantity based on the Txn Type (Col C) and the Stock (Col D).

For instance, if Txn Type = “Add” and Stock = “Water” then the formula in the corresponding Water cell for that row would reflect the increase in Qty (Col E).

If the Txn Type = “Add” and Stock = “FG” then the formula in Col F (FG) would reflect an increase based on the Qty in Col E. The formulas in Cols G:I would multiply the units in Qty times the respective raw materials units required for one FG unit. Each of the raw material formulas would be wrapped in a =ROUND function and the sign would be reversed to reflect a reduction in the respective raw material.

A Txn Type “Deduct” and Stock “FG” would result in a Col F deduction by formula of the negative value entered in Qty (Col E).

The =SUM formulas in F2 through I2 will reflect a constantly adjusted current balance for FG and each raw material.

This is a relatively simple layout, but again – you are forcing a spreadsheet to do what would be better accomplished in a database application.

Hope this helps.