Im happy to help further, as long as you have defined the problem fully. Thats because I think I can provide a solution in a relatively short time. If however it becomes "the thin end of a wedge", I may have to leave it with you.
Give me a little time, and I will work on your attachment aiming to deliver your three objectives.
Take a look at the attachment. For the moment, just play with this data to see how it works, and then set up your CusData and Workings on this model. To demo, you just need to select one of the Customer IDs from the drop down list in C1. Let me know what you think. When you have your data organised, I can then take you through how to import the formulae onto it.
If there is anything you want to change, please explain clearly, as it might mean modifying the formulae. Ive gone with the option of having a separate sheet for the Customer database, but if you like it could all be on the one sheet.
on cell J2 on worksheet "workings" can we limit it to 2 decimal places for grams?
On this amended attachment, the formula in Col J caters for grams to two decimals and Lbs to one decimal. Mine starts in J4 as previous. If you need it to start in J2, then copy up from J4 to J2:J3.
making 100 kilo vs 343 kilo would change components
thx
You can make any changes you wish to the recipes in CusData, and these will be carried across to Workings. You can also add new customers and recipes (down to row 50 at present).
When you say making a batch of 100 kilos vs. 343 would change components, why not just have your customer database show the recipe mixture for (e.g.) 1 kilo (all customers)?
You can then introduce a multiplier to calculate the total component volumes for any batch size (on the workings sheet).
Then you would only need to change your customer database if the recipe proportions were changed.
OK - Look forward to hearing from you
As I have little knowledge of your objectives in terms of functionality of the data, I do not feel comfortable criticising your efforts, or feel able to suggest alternatives.I am hoping you will critique it with a better alterative ;-)
Notice - this should be 1 lbs and 99.79 grms