Inventory Management - Location update

CJP

Hi all,
I'm working on an inventory management system that has the following sheets:
- MASTER inventory
- Incoming transactions
- Outgoing transactions

My question is how to I get the MASTER inventory to show a location based on transactions entered into the incoming transactions tab?

Currently the items are entered manually into the Master inventory and then current stock will update when an item is entered into the incoming transactions sheet via a form using SUMIF functions matching rows using 'item name', 'category', 'sub-category', 'size' and 'team'. There could be multiple entries in the incoming transactions sheet with the same criteria. For example I may enter 3 size 8 stan smith shoes today and another 5 tomorrow. The Master inventory would update current stock accordingly (8 of this item added to the current stock column).

I would need the latest incoming transaction to set the location and override any previous locations that match the same criteria of 'item name', 'category', 'sub-category', 'size' and 'team'.

Itai Perez

Hi @CJP

I hope I understood correctly. You want the location of the item to come from the latest form entry?

If this is the case, in the entry form sheet, are new rows entered at the bottom or top of the sheet? If at the top, you can use VLOOKUP to capture the latest location according to the parameters you set.

CJP

Hi @Itai , thanks for your response. You understood correctly. The new rows are entered at the bottom of the sheet. I will look into how I can get them entered at the top. Cheers, CJ.

CJP

I've now got the form entering the rows at the top of the sheet. However, now I am struggling to get a VLOOKUP to work. I'm not sure that is the appropriate formula for my situation as there are too many criteria that need to match. Would appreciate any suggestions from the community :-)

Itai Perez

You can create a helper column with the JOIN formula on all your criterias and that will mean you only need to compare one criteria (that will include all of them) so than VLOOKUP will work.

CJP

Thanks @Itai that's something I've already tried and currently can't get it to work, just returns errors. Will keep playing around see what I can do.

Cheers,
CJ