Inventory Management - Location update
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'.
Answers
-
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.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
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.
-
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 :-)
-
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.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 409 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!