inventory

[Tiu001]

i hope you can follow me here, i want to create a spreedsheet of outgoing materials for the day on one tab and on a linked tab have my inventory. every morning i want to hand out the days list of materials to be used on tab one, that would subtract from the inventory on tab two. thats easy enough, but is there a formula that will hold that new inventory quantity if i zero out the first tab contents in order to start fresh the next day with the updated inventory quantity?

[#2]

@Tiu001 

You can achieve this by using a formula to calculate the new inventory quantity on the second tab based on the outgoing materials on the first tab. Here's a simple example of how you might set this up:

Assuming you have the following setup:

• Tab 1 (Outgoing Materials): Column A contains the names of materials, and Column B contains the quantity used for the day.
• Tab 2 (Inventory): Column A contains the names of materials, and Column B contains the initial inventory quantities.

You can use a formula in Column C of Tab 2 to calculate the updated inventory after subtracting the quantities used from the outgoing materials. Let's assume the formula starts in cell C2 of Tab 2. You can use a formula like this:

=IFERROR(B2-VLOOKUP(A2, 'Outgoing Materials'!$A$2:$B$100, 2, FALSE), B2)

Here's how the formula works:

1. VLOOKUP(A2, 'Outgoing Materials'!$A$2:$B$100, 2, FALSE): This looks up the material name in 'Outgoing Materials' and returns the corresponding quantity used. If the material is not found, it returns an error.
2. IFERROR(B2 - ..., B2): This checks if there was an error in the VLOOKUP (i.e., if the material is not found). If there was an error, it returns the original inventory quantity (B2). If there was no error, it subtracts the quantity used.

You can drag this formula down for all materials on Tab 2, and it will calculate the updated inventory quantities based on the outgoing materials. When you zero out the quantities on Tab 1 for the next day, you can repeat the process.

Adjust the cell references based on your actual setup and the number of materials you are dealing with. The text and steps were edited with the help of AI.

Additional Information/link: MS Templates, results for inventory

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.