One large problem with your approach is that everything is on one worksheet. Excel, and VBA work best if different types or groups of data are on different worksheets.
- Arrange the basic systemic data so it is very easy for Excel. (systemic = Products and factories.) Each System Unit gets its own sheet
- Design various User Friendly worksheets to present details and summaries to people. Each such Report sheet is reasonably specialized.
- Use VBA and Excel to fill out those Report sheets
- Create a VBA "Orders" UserForm for people to input Order details and a sheet to list those values in an Excel friendly format.
- Design Factory Staus sheets to list Factory Status in Excel friendly format.
- Use VBA and Excel to analyze Factory status and Order details.
- Create a VBA Userform "Assignment Orders" to present the result of the analysis to people so they can assign Orders to a select Factory.
At this time, with little knowledge of actual procedures, I am leaning towards a standardized Factory Class module and VBA code that uses Factory Class objects. Possibly a standardized Order Class Module.
See attached for Excel Friendly Systemic data sheets. The layout is critical, only the top two rows are formatted in any way, an empty column is required between different groups of data types. "LONDON" and "ENJOAR" are examples of possible data sheets for factories and Products.
Most Systemic data sheets can be hidden from the Users.
The Factory details are included on Product sheets because Product lines change much more often than Factory details.
The actual details on each example are limited to what I could find on your last attachment.
Reply With Quote