Devide total project hours randomly

[oddy73]

I have been working on this project and I have come very far, with the help of many excel forums...
The final piece of the puzzle is very tricky and I need your help with it.

So I have created a time sheet and its input comes from external systems. Once the data is imported in the sheet, via excel formulas a monthly time-sheet is populated. At this moment the population process works perfect (see attachment and change the Column A and the percentage in column G)

Unfortunately the requirements changed and the solution we have now is not as wished. In order to finalize this project, the random function should implemented. This means the following:

Current state represents the assigned hours according to the following elements:

• Number of holidays or being sick and absence…
• Number of projects according to their percentage
• And date

The wish is now to randomly divide the assigned hours over in the project section -J13 AN18-

I have attached the sheet and you can see my what I mean in sheet2 (the arrow). The button was my way to solve the random function, but forget is...

The idea behind the random function is that a person can work in a day on different projects, and the wish is have the project section each day randomzid number but the total must be the sum of what is given in J13:AN18...

​I was wondering if we could use our current solution and build further. What I mean is to use the result that we have now in the project section and divide it over the amount of assigned projects randomly.

Thanks in advance!

Attachment.

 

[oddy73]

I post this thread in another form, but I was not able to get this solved.

This time I (together with a friend) tried VBA and believe it or not we came very closer, but not yet.
As you can see, the generate button, generates numbers between 1 and 8.

Due to our amateurism we were not able to create good and structured code. Hopefully you can make a since of this and find a workaround to solve my mystery since the code has some limitations.

1. The range where the random numbers are generated is the whole section J21:AN26. So also weekends…
2. Another issue that this code has is that it takes always 8 hours and does not use the value “available for projects” in section J13:AN13 (if this is solves than the first problem is also solved)
3. The amount of projects is fixed value in the code (numRows = 3). This code should use the orange section of the sheet.

To summarize this whole excel project (context).

Vacation and absence data are pulled from other systems. Another tool I developed generates the project deviation (this is the orange section). Once these two items are inserted in this monthly timesheet, automatically the timesheet should get its data from the vacation and absence section and for the project section the random function should be applied. At the end I would have this all automated and by a push of a button the a whole month is created.

Source