Stock Inventory Amendments using vlookup?

Originally Posted by btlewy

Is there a simple way to do this?

That all depends on your definition of "simple". If I'm understanding you correctly - you have a lookup sheet that you want to be able to alter to update the source sheet - then this can't be done without vba. I don't think it's particularly complicated vba, but it's vba nonetheless, so you'd be left with a macro-enabled workbook. Assuming you're okay with that, you can certainly find help here, but you'll need to post a small sample workbook (Use: Go Advanced --> Manage Attachments to access the upload window) with all private data altered or removed.

Hi btlewy and welcome to the forum,

I think you shouldn't work between two different sheets. If you have your data in an AutoFilter Table you could simply add a row on the bottom of the Stock Inventory and it would be included automatically.

https://www.timeatlas.com/excel-autofilter/

Hi CAntosh - Thank you very much for responding

I had assumed that vba would be involved somehow but I'm not very confident with it at all, so wouldn't know where to start

I uploaded an example as requested-
Sheet 1 (Location by Product Search) - When you type a SKU number in the search results will display the location and quantity on hand of that product. Some SKU's are stored in multiple locations throughout the warehouse which is why it provides the space for up to 30 search results. This sheet uses VLOOKUP to obtain the data from sheet 3 (inventory).

What I would love to do if its possible - On sheet 2 (Inventory Management) I would like a user form where I could enter a SKU number, Location, and then either a number to add or subtract from the current count located on sheet 3 (inventory) Once I hit update the fields would clear and then allow a new entry and the new stock count would be reflected in the 'inventory' sheet for that particular sku at its unique location. I hope this makes sense.

Might be pushing my luck but... if this is achievable by vba is it possible for the SKU field to accept both manual entry or entry via barcode scanner???

Thanks again for looking at this - i really appreciate it!!

MarvinP's idea of keeping your data in a table and using filters and pivots to display it is one that's worth exploring. I don't know how familiar you are with tables, but it's generally easier to learn to manipulate tables to your liking than to have to tweak VBA code whenever you need to change the structure of your workbook.

That said, a VBA solution shouldn't be too tricky. When I tried to envision the "user form" you requested for sheet2, it kept looking like sheet1, so I blended the update into sheet1 - take a look at the attachment to see if it's to your liking. I ignored sheet2 and simply added a "New Quantity" column to sheet1. Enter whatever the new quantities should be in that column, click the "Update Qty." button, and the VBA procedure should find the location on your inventory sheet and update the associated quantity. The code used is as follows:

Please Login or Register  to view this content.

I did some unmerging that may have disrupted some of the aesthetic choices on your initial sheet, so you may have to re-merge some areas to get back to your original "look". Just don't do any merging that involves the location or new quantity columns; VBA doesn't like merged cells.