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:
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.
Bookmarks