Automate updating inventory on an existimg sheet?
Once a month we receive 5-7 files in csv format from vendors with an inventory update. I made each file into a sheet and then made a report with all sheets to have a master sheet. We add columns to track what inventory needs to be reordered and when an item is about to expire or needs to be tested to extend the expirstion date. These are all columns we add and set up manually.
The issue comes when its time to update line items. We get new files from the vendor which may contain new line items. This causes the row postion of an item on a sheet to change. So if we simply copy and paste the new data into the sheet to override the old data, the columns we set up detailing reorder levels and retest dates wont match up. While I am hoping we can ask the vendors to give us a file where any new line items are all the bottom lines in the file, I think it would still present a large opportunity for error as we would need to verify each row matches prior to transferring the data from the vendor file to the smartsheet.
Im stumped on a solution that doesnt require a ton of manual checking by eye.
Answers
-
This could be a job for Data Shuttle - there might be a few options Data Shuttle has that could help.
One being Data Shuttle can recognize changes or new lines on a source file, and only update those values in the target sheet (ie. your smartsheet). The 'key column value' could be the inventory item #, or a unique combination of item # and vendor name.
If you do not have data shuttle, you can set up a demo with your customer success manager.
Or a series of Vlookup formulas to pull data from your vendor sheets into the master sheet
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives