+ Reply to Thread
Results 1 to 5 of 5

Introduction and need help figuring out inventory workbook idea

  1. #1
    Registered User
    Join Date
    02-16-2018
    Location
    WI
    MS-Off Ver
    2007
    Posts
    3

    Introduction and need help figuring out inventory workbook idea

    I'm comfortable using excel but on occasion I run into problems, issues, and trouble figuring out how to get things to work the way I want em to. I use Excel 2007 at work as well at home in my hobby shop for inventory, calculations, label making, charts, etc.

    I am hitting dead ends on how to get an idea for my inventory workbook to work so any help would be much appreciated in figuring this out.


    So I just picked up a barcode reader and would like to incorporate it to my inventory sheets. With a barcode scanner it scans and enters the barcode and moves onto the next cell. My Idea is to have a cell where you scan a barcode in then in the cell below that you can enter the new quantity on hand of the part just scanned. So for the inventory table there will be a barcode column, part description column, and a quantity column.

    Here is where I get stuck.

    Is there a way to get a cell (new quantity) to change the data in another cell (quantity in the inventory table) using data in another cell (barcode) as a reference to find that particular cell in a table (inventory table) and then have the cell in the table retain the new data when a different number is entered in the barcode cell?

    My end goal is to be able to scan a barcode and enter a quantity and hit enter to change the quantity in the inventory table for that particular part. I am trying to make counting inventory easier and faster without needing to scrolling up and down through spreadsheets to find that particular part and replace using ctrl F (find and replace)

    Hope that makes sense and any help would be much appreciated.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,550

    Re: Introduction and need help figuring out inventory workbook idea

    .
    I can't assist you with coding this project. There are however lots of postings on this forum and others where others have accomplished what you desire.

    As a side thought ... have you considered using one of the many free apps for your cell phone ? Some of the free apps will scan & download the data to Excel.
    The use of a free app will cut your time (programming) and costs significantly (scanning hardware).

  3. #3
    Registered User
    Join Date
    02-16-2018
    Location
    WI
    MS-Off Ver
    2007
    Posts
    3

    Re: Introduction and need help figuring out inventory workbook idea

    I have and been searching/reading through the postings but have not found anything that has helped me figure this out as of yet. I have searched the terms "inventory" and "inventory retain". If you have any terms that you would think would help find the postings that would be great. Ether way I will keep digging.

    I have had the same thought and looked into the apps but have not found one that we liked plus we already have wireless scanners purchased. We plan on starting excel for inventory once I have finished printing and placing the bin labels. The workbook will be used for several tasks: Writing up quotes, inventory, bill of materials on job sites. Till I figure this out I'll just use the Ctrl+F for inventory counting.

    You mentioned programming, are you referring to macros? If so I am not familiar with that as of yet and would like to learn. Do you or anyone have any recommended sites or books that would aid in this?
    Last edited by R.Anderson; 04-06-2018 at 04:18 PM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,406

    Re: Introduction and need help figuring out inventory workbook idea

    I am surprised that someone hasn't already said, "Use a database!"

    While this might not meet your needs, it does explain how a transaction-based system works.
    https://www.excelforum.com/tips-and-...y-program.html.

    You start with nothing. You take in 10 units of Acme Putty. You build widget A that uses two units of Acme Putty. So you create a transaction to remove 2 units of putty from inventory. You get 10 more units of putty. So now you have 10 - 2 + 10 units of putty. At no point do you ever store the total of anything*. It's all a sum of the transactions. With the right kind of data layout, you can do this with pivot tables.

    Some of the transactions may be manual. You do a periodic check of your inventory locations on a schedule (cycle count) to see if what the databases says is there is actually there. So you should have a transaction type called Inventory Adjustment. These can be plus or minus. It could be shrinkage or somebody moved something and didn't record the proper move transaction.

    * Once in a while, you stop everything except maybe small operations in "quarantine" (like take stuff in on the dock but don't put it in the computer yet) while you count everything in every location in the warehouse. This would be a time to archive the old data and scan everything in new. It might be worth while to give this transaction type a special name like "Physical Inventory."

    By keeping track of transactions you can get a feel for material flow rather than static information.

    This is way too much for a hobby - you can probably get along with two transactions: IN and OUT.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  5. #5
    Registered User
    Join Date
    02-16-2018
    Location
    WI
    MS-Off Ver
    2007
    Posts
    3

    Re: Introduction and need help figuring out inventory workbook idea

    Thanks dflak, I will look into a database.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Workbook has a macro that I have no idea how to update. I just want to add columns
    By dangerdavedsp in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 09-12-2017, 07:17 AM
  2. [SOLVED] Unusual Inventory Workbook Need help
    By cat3rn in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-31-2017, 12:41 AM
  3. how to use autofill from inventory workbook on invoice workbook
    By ahmed1234 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-20-2015, 06:46 AM
  4. Replies: 3
    Last Post: 09-11-2013, 10:41 AM
  5. creating inventory workbook
    By jladdm3 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2008, 08:56 AM
  6. Protected Workbook - Not a Good Idea!
    By Karen in forum Excel General
    Replies: 2
    Last Post: 07-06-2006, 12:25 PM
  7. [SOLVED] Inventory a WorkBook
    By CLR in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-29-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1