Inventory and Barcodes

sandbergk2

Hey there!

I have a question regarding inventory management and barcode scanning. Currently I have a sheet called "inventory tracker". We have various different lab supplies that need are deployed depending on the need. Currently, we are manually counting how many supplies we take out and whatever is not used, we count and add back into the "inventory tracker" sheet.

The goal is to automate our process. I have figured out how to use the smartsheet mobile app to scan item barcode to match a specific supply. And I would like for our team member(s) to scan an item, the item/sheet pop up on their mobile app and then update the qty in real time.

Another thing to add, I have another sheet called "inventory check out", ideally, I would like to keep the "inventory tracker" as the master file untouched by team member(s). But instead have the "inventory check out" sheet sync or somehow match with the appropriate columns. Essentially, once a team member scans the item barcode, I would like to build out a system where the sku # pops up the item, then the person checking out the specific supply/supplies can specify how much was taken out or put back in the inventory count. I would love for the two sheets to communicate to each other somehow. Please advise.

Note: Barcodes have not been formulated as of now, but will be soon*

Thank you for your time!

Kristina

Dan Beres

Hi Kristina,

That sounds like a very interesting use-case for Smartsheet. I could be over-engineering the example here and depending on how long this tool is used you could hit the sheet cell limit of 500,000 (meaning of you had 2 columns, you could only have 250,000 rows or 5 columns and 100,000 rows).

Two tools I think are integral to this solution whether it is mine or one you formulate are:
VLOOKUP - based on a certain criterion (in this case barcode), will be able to reference other information
Sheet Reference - when using a formula, you may see something like "Reference Another Sheet" - this is a way to get information from other sheets without having to have the info/calculations present in your main sheet.

I would start out with having a reference sheet that corresponds a Barcode with an item's name, SKU, or any other static information. This can be used to automatically populate information using VLOOKUP in column formulas whenever you sign a barcode into a cell it will fill out some information for a confirmation that the item was read correctly. An example being:

=VLOOKUP([Barcode]<number>, {Static Inventory Range 1}, 3, FALSE)

• [Barcode]<number> being the cell that you would scan into
• {Static Inventory Range 1} would be the reference to another sheet that you need to set up when starting the formula
• 3 is just an example but it would look at the 3rd column and return that value in your reference range. Example Column 1 = Barcode, Column 2 = Item Name, Column 3 = SKU, this example would return the SKU
• FALSE meaning that it will return an exact match instead of the first approximation it finds

With this in mind, my first thought is to have:

• Static Item Information Sheet: names, barcodes, SKUs, etc. Other sheets will reference this.
• Checkout Sheet: Scan the item, have it populate the name and description to check with a VLOOKUP, and then specify the amount taken in one column, and then amount returned in another. Dynamic Item Info will reference this.
• Dynamic Item Information Sheet: This is where I would do some calculations. SUMIF statements where you have a base value of the item and then it does a calculation based on the checkout sheet, subtract the SUMIF value if it is in the checkout column, add the SUMIF value if it is in the return column. Inventory Track will reference this.
• Inventory Tracker: This would be the front-facing master file that you can leave untouched and even lock it if you would prefer.

There are a lot of moving parts that can make this solution a bit more complex, but these are just my initial thoughts. If you have any other concerns or would like me to elaborate on anything I would be more than happy to help!

Best,
Dan

sandbergk2

Hey @Dan Beres!

Thanks so much for your response and insight. I did have a follow up question on the formula you provided. Which cell would this formula fall into?

=VLOOKUP([Barcode]<number>, {Static Inventory Range 1}, 3, FALSE)

Thanks,

Kristina

sandbergk2

@Dan Beres Is there a way we can connect to assist further? Thanks!

Dan Beres

@sandbergk2 You can reach out to me on my LinkedIn if you would like further assistance, same name and profile picture.

That formula can go into several columns, and then be turned into a column formula so it applies to all existing and future rows that are added. Depending on what you want the VLOOKUP to reference, it can be a SKU, Item name, etc. whatever you want the reference to be. In the example I gave with the setup I had it would be 3 = SKU so I would make a new column that would pull the SKU of the item based on the barcode.