Hours allocation worksheet .

**Scoobybrown** · 02-18-2010, 06:20 PM

Hi Guys,

This is my 1st thread on here. I have only a very limited knowledge of macros (colouring cells, calculating coloured cells, clearing cells etc.

I have maybe stupidly agreed to complete a project that entails producing a spreadsheet that show the tasks that employees will perform and the time allocated to each task.

I have attached a very simple example of a worksheet. Basically, I have a number of jobs that have tasks to perform. The tasks all have time allocated to them and some are fixed (Tasks C-G) The times in Task B are what is needed to complete the task.

Task A is only allocated time remaining by adding the other tasks together and subtracting it from the daily working hours (C2)

The tasks A & B must be equal to the totals in row 17.

What I am looking for is a macro or some guidance that will enable me to do the following:

From the top job down, allocate the remaining time to task A. Working down the list of jobs until the time allocated to task A equals the total for task A in row 17. After this time is allocated I would like it to leave the remaining jobs with no time against them for task A. The aim is to maintain as many full 12:00 jobs as possible whilst making the rest part time if required.

If part time jobs are required I would then like the macro to look at task B. Again working down from the top of the now part time jobs I would like to make them up to 12:00 by performing task B for other now part time jobs and reducing them even more. If this is possible I would like the task performed for another job to be shown in the 2nd B task column and perhaps but the job performing the task and the job it is being performed for be highlighted with the same colour. Task B should not total more than total in row 17.

Job 1 does not have enough time to perform task B (-0:10) so in cases like this I would like to once again get other jobs to perform the task time that job 1 does not have enough time for and like before highlight both jobs with colour.

I have previously done most of this manually but the time has come for me to get out of my safety zone!

I’m really not sure how easy/hard this is to do but I fear I have bitten off more than I can chew! I’m not even sure if my explanation is even understandable!

Any guidance would be great and I am prepared to learn and work hard on this. I just need a shove in the right direction!

Thanks in advance and please feel free to ask me to explain my ramblings!

Phew!

**Scoobybrown** · 02-19-2010, 02:15 AM

Perhaps I have asked too much.

Can anyone tell me the type of things I need to go away and look for at to how to do this?

Basically need one or two columns to add up to a given total whilst making sure that the row total also adds up to a given total until the column total is reached.

Thanks

**teylyn** · 02-19-2010, 04:49 AM

Hi Scoobybrown,

the job you are detailing above is really a full-fledged application development, rather than a question on how to resolve a particular error message or find a formula to perform a calculation.

The helpers here are all volunteers who put in their free time to answer questions about Excel functionality. We normally do not provide a full application development service.

You may be better served to start developing your application yourself, and if you run into a particular problem, ask about just that. I'm sure you will get responses.

The scope you've outlined above is probably days worth of development effort (I assume. I'm not a VBA developer) and can't be answered without more detailed information and sample data.

So, my advice is to break this thing down into smaller pieces and ask for targeted advice on individual issues.

regards