Moving Stock between Warehouses

Hi all. I'm developing a spreadsheet to manage stock between two different warehouses and have hit a stumbling block; I have attached the spreadsheet in question.

I want users to be able to "move" items from one warehouse to another by entering the data into the STOCK TRANSFER sheet. The user should be able to enter the product ID into cell A3, with the corresponding item description returned from the ITEMS LIST sheet. One they've entered the quantity into cell C3 I would like the two buttons to either:

Move the desired quantity of the chosen item from the OFFICE sheet to the corresponding quantity on the CRISPINS sheet or
Move the desired quantity of the chosen item from the CRISPINS sheet to the corresponding quantity on the OFFICE sheet.

I have tried to record a macro to each button however I can't get it to work correctly e.g. if user enters 10001001m12 into cell A3 and 10 into C3 then clicks Move to Crispins it should return "Neo Mix box 12" in B3, before subtracting 10 from cell B12 on the OFFICE sheet and adding 10 to cell B12 on the CRISPINS sheet.

Apologies in advance if I haven't explained it clearly enough and I welcome any suggestions on a better way to do this.

Thank you very much, it works brilliantly!

You'd be better off doing yourself a huge favour and paying a few quid per month for a professional web based system. Excel simply doesn't work well enough for stock systems

Originally Posted by Kyle123

You'd be better off doing yourself a huge favour and paying a few quid per month for a professional web based system. Excel simply doesn't work well enough for stock systems

I appreciate the advice. How do you determine when your needs outstrip excel enough to justify that move? What particular deficiencies do you think Excel has regarding stock systems?

Well one of the first things to consider is the commercial viability of using a spreadsheet; how much time have you already spent on the spreadsheet and how much time are you likely to spend making it do what you want it to? Weigh up the projected hourly spend against what you would otherwise be doing, how much your time costs and see how it compares to a purchased solution.

You then need to look at the shortcomings of Excel, remember it's just a fancy calculator, so as soon as you need to start doing anything other than adding up things start getting complicated, you need to start hacking around and using macros to make it do what should be simple things. Some things to consider:

- Excel has no inbuilt tracking, anyone can change anything mistakenly or otherwise and it's impossible to tell who changed what. Consequently it's very easy to end up with incorrect data and it's impossible to undo. In contrast, a proper stock system will be database driven, this allows the tracking of everything, even historically.
- You have much more control over a professional system, you can allocate user permissions and set data validation rules so it's very difficult to enter incorrect information. Users cannot add/edit stock items for example and can have clearly defined roles
- Professional systems will have interfaces for proper stock taking, will adjust stock from orders and sales; this will all be fully audit able and it is trivial to get things like stock value, alerted when levels drop below defined levels
- Multi-user access, there is no neat solution to this in Excel, it simply is not built for a multi user environment - if more than one person needs to alter stock levels etc at one time Excel falls down
- Integration with other services, most stock systems will integrate with popular e-commerce platforms if you sell online; they offer automated POs when stock levels get low. They will usually handle financial reporting too, or offer interfaces to popular accounting packages

The plethora of stock systems out there mean that pretty much any stock requirements you have will already have encountered and resolved by other businesses. Trying to roll your own system means that you have to re-invent the wheel each time you realise something doesn't work as you want it to - commercial systems will have found and resolved issues you haven't even considered yet.

That brings me on to the issue of scope, without exception all stock systems I've ever seen have grow hugely in scope from being simple to hugely complicated hacked together solutions with bits bolted on here and there. Before you realise, you end up with a business critical maintenance nightmare that takes a huge amount of time to administer. This also makes the transition to a proper stock system down the line all the more painful as the data migration is hard; Excel stores data quite differently to how it would be stored in a database and therefore provides a barrier to entry. It's much better to avoid this and accept that there are very few stock requirements that haven't already been solved commercially.

Price wise web based solutions tend to be very reasonably priced - the competition is pretty fierce so you can take advantage of it. Most products will also offer free trials so you have very little to lose.

I've seen this a lot, and while Excel seems like a very cheap and simple solution, it never ends up like that unfortunately

That's why for stock systems, I always advise people to got for a professional one; Excel quite simply isn't suitable - remember, it's really just a calculator