Formulas

[CtrSam]

Howdy folks!

Working on a project an need y'all's help! An example

I have 2 sheets 1st sheet is labeled bags, 2nd sheet is labeled total on hand.

1 BAG EQUALS 2 APPLES, 3 PEARS, 4 ORANGES, 3 KIWIS

On the 2nd sheet I have the total of all items on hand.  So lets say 20 Apples, 20 pears, 20 oranges and 20 Kiwis.  On the 1st sheet I have cell labeled bag and on the cell next to it is my labeled quantity;  What I am looking for is items subtracted from the total items on hand via the amount of bags.  So if I were to input 1 in the quantity cell on the first sheet I want it to subtract the quantity of the items amounted to what 1 bag equals.  Quantity; 1 bag I want it to subtract 2 apples, 3 pears, 4 oranges, and 3 kiwis from their totals. quantity 2 bags I want it to subtract 4 apples, 6 pears, 8 oranges, and 3 kiwis.  So on and so forth.

[#2 · Accepted solution]

Hi @CtrSam,

 you can try this idea for subtracting items from the total items on hand based on the contents of a bag:

Step 1: Create a lookup table to map the items in a bag to their quantities.

For example, your lookup table might look like this:

Item | Quantity
------- | --------
Apple | 2
Pear | 3
Orange | 4
Kiwis | 3

Step 2: Create a formula in the total items on hand sheet to subtract the items from the total items on hand based on the contents of a bag.

The formula would look like this:

Total items on hand - (Quantity of item 1 in bag * Number of bags + Quantity of item 2 in bag * Number of bags + ... + Quantity of item n in bag * Number of bags)

For example, the formula to subtract the items from the total items on hand based on the contents of a bag of apples would be:

Total apples on hand - (2 apples/bag * Number of bags)

The formula to subtract the items from the total items on hand based on the contents of a bag of pears would be:

Total pears on hand - (3 pears/bag * Number of bags)

And so on.

Step 3: Enter the formula in the cell next to the cell where you have the number of bags, and then copy and paste the formula down the column.

Here is an example of how to use the formula in a spreadsheet:

Sheet 1: Bags
Cell A1: Item
Cell A2: Apple
Cell A3: Pear
Cell A4: Orange
Cell A5: Kiwis
Cell A6: Number of bags
Cell A7: 1

Sheet 2: Total on hand
Cell A1: Apples
Cell A2: 20
Cell B1: Pears
Cell B2: 20
Cell C1: Oranges
Cell C2: 20
Cell D1: Kiwis
Cell D2: 20

Formula in Sheet 2:
Cell A3: =A2 - (VLOOKUP(A1, Sheet1!$A$2:$B$5, 2, FALSE) * A7)
Cell B3: =B2 - (VLOOKUP(B1, Sheet1!$A$2:$B$5, 2, FALSE) * A7)
Cell C3: =C2 - (VLOOKUP(C1, Sheet1!$A$2:$B$5, 2, FALSE) * A7)
Cell D3: =D2 - (VLOOKUP(D1, Sheet1!$A$2:$B$5, 2, FALSE) * A7)

If you enter 1 in cell A7 on Sheet 1, then the formula in cell A3 on Sheet 2 will subtract 2 apples from the total items on hand. This will result in a value of 18 in cell A3 on Sheet 2.

You can copy and paste the formula in cell A3 down to cells B3, C3, and D3 to subtract the items from the total items on hand for pears, oranges, and kiwis, respectively.

This formula will automatically subtract the items from the total items on hand based on the contents of a bag, regardless of how many bags you have.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)