I have a calculator that I am needing some help with. I am so-so when it comes to excel, I can usually piece-mill calculations and some random vba I find on the web to accomplish what I need but this calculation is eluding me. Disregard the Operators/Assemblers Calculator as it has no immediate impact on the Completion Date Calculator, which is what I am needing help with.
Below you will see a snapshot of my worksheet. B= start date, C= Order amount, D&E are accumulated to account for 1 type of employee and so are F&G, H= is the selectable part being produced and then I6:I10= completion date, I3 and K3 being editable variables for the amount of employees employed at any one time.
What I am attempting to do is output the next available date to start a project as orders are being taken in on a daily basis. The calculator would allow you to take orders in on =Today() until either I3 or K3 has been exceeded. Once those have been exceeded the data validation needs to kick in and only allow you to take in an order on the date with the next soonest completion date. (all of this I've accomplished up to the point of moving to the next date, moving to the subsequent is where I get stuck).
Here is where it gets tricky and I believe it is going to require VBA. I need it to look at the next available date and know that if it is I.e. B12=12/18. This would then free up the employees on lines D6:E10, and F6:G10 respectively that coincide with a completion date of 12/18. Those employees could then be reassigned on that date up to and until the number of employees is then exceeded again. At that time it needs to roll down to the next available date and complete the process until the sheet is either cleared or the completion dates arrive and the job can be deleted.
Thank you in advance for any help with this issue.
Below you will see a snapshot of my worksheet. B= start date, C= Order amount, D&E are accumulated to account for 1 type of employee and so are F&G, H= is the selectable part being produced and then I6:I10= completion date, I3 and K3 being editable variables for the amount of employees employed at any one time.
What I am attempting to do is output the next available date to start a project as orders are being taken in on a daily basis. The calculator would allow you to take orders in on =Today() until either I3 or K3 has been exceeded. Once those have been exceeded the data validation needs to kick in and only allow you to take in an order on the date with the next soonest completion date. (all of this I've accomplished up to the point of moving to the next date, moving to the subsequent is where I get stuck).
Here is where it gets tricky and I believe it is going to require VBA. I need it to look at the next available date and know that if it is I.e. B12=12/18. This would then free up the employees on lines D6:E10, and F6:G10 respectively that coincide with a completion date of 12/18. Those employees could then be reassigned on that date up to and until the number of employees is then exceeded again. At that time it needs to roll down to the next available date and complete the process until the sheet is either cleared or the completion dates arrive and the job can be deleted.
Thank you in advance for any help with this issue.
