How to make changes in one Google Sheet column auto update other columns?

Question

Example Google Sheet - Grocery Store.

On the sheet 'Stock' there are the items for sale (column Name) and the quantity for each item (column Quantity).

On the sheet 'Sales' the name of the item sold will be selected from a dropdown list and the number of items sold should be entered manually.

What needs to be done:

Create a Google Sheets function =SELL(n), where n = number of items sold. The function should have the following properties:

a) checkName - check the cell to the left for the item name and find in in 'Stock' tab, for example 'Apples'

b) decreaseQty - Decrease Quantity in 'Stock' by the number of items sold. For example, if we had 10 Apples in Stock and sold 1, there should be 9 left.

b) increaseSold - Increase Sold items in 'Stock' by the number of items sold. For example, if we had 5 Apples sold and sold 1 more, there should be 6.

c) (Optional step). Nr. of items sold greater than Quantity in 'Stock' should not be allowed.

Your help would be greatly appreciated.

You forgot that these foods are perishable so in a week they will probably all go bad anyway. You do not need a script or macro, you should never use a script or macro unless you absolutely cannot do it from within google sheets with regular formulas. — CodeCamper
If you are looking for a code rather than cell formulas, you should make some attempt to write a code yourself and people will happily help you where you got stuck. As for the general workflow - either with script or cell formulas - it will be easier if you implement auxiliary columns: A column for the initial quantity - e.g. 10; a dynamic column for total sold items -e.g. 1; and a third column that looks like your column B: 10-1. To create the third column you need to stringify the values, e.g. with the cell formula '=CONCATENATE(D2,"-",E2)'. — ziganotschka

CodeCamper CodeCamper · Accepted Answer · 2019-12-18T23:11:49

The way you have your data currently set up is not optimal for what you are trying to do, instead you should have a list of transactions, the first being beginning balance and then use negative numbers to represent sold. Your inventory should be calculated from your list of transactions.

For example you will make your list of transactions look like this:

Apples  10
Oranges 14
Tomatoes    21
Potatoes    47
Apples  -1
Oranges -5
Potatoes    -2

How to make changes in one Google Sheet column auto update other columns?

1 Answers