How do you update the quantity for an inventory item that appears in several lists on the spread sheet
I have a spread sheet I am putting together that helps me keep track of the components for 12 different kits. Items and quantities required for each kit are listed in their own "bills of material" on the spread sheet. However, some of my inventory items are components in more than one kit. In Excel 2013, is it possible when I change the quantity on hand for that component in one bill of material, that the quantity on hand for that same component will be updated in the other bills of material. Otherwise, I have to go into each bill and manually change the updated quantity, which is time consuming. If this can be done, please give me some help. Thank you.
| Attachment | Size |
|---|---|
| sample spreadsheet showing issue of updating inventory multiple places.xlsx | 8.64 KB |
Recommendation
Hello,
This can be done a number of ways (espsially with VBA), but I am going to go over the way I would recommend for your situation.
Preffered method :
1. Create a new sheet titled inventory. On this sheet list out every item you could possibly have on hand in column A, in column B, indicate the quantity of each item you have on hand.
2. Next, on your sheet which contains each Kit (The sheet you provided), use this formula in cell C5 (Your first item in Kit A).
=VLOOKUP(A5,Inventory!A:B,2,FALSE)
3. Now copy this formula in each cell in column C on the sheet you sent over. Cell C6 would show =VLOOKUP(A6,Inventory!A:B,2,FALSE). The bolded cell is the only thing that changes when you copy/drag the formula.
4.Now you can update your numbers on the inventory page and it will update the number for every kit that contains that item.
Let me know if this doesn't work for you and we can come up with another solution.
Sincerely,
-Max