create report from selected information

**nikoniko** · 10-02-2009, 06:24 AM

Hi All

I have a head scratcher for the weekend, I don’t expect anyone to do the lot for me, I know it is probably a lot of work, but a good start would help me tremendously.

I have attached a (dummy) worksheet which contains workers, their projects and hours, an area showing their availability (which is just 37.5- their recorded hours for a given week and their pto hours) a n area for pto, and finally an area for utilization which is ((35.5-availability)/35.5*100.

I have been asked to use VBA to produce the following, in either a report or table form:
1- Total # of resources with >25% availability in any given week, then break down by resource
2- Total # of resources with >25% over allocation in any given week, then break down by resource.

For the first one, >25% availability would be anyone with more than 9 hours for any given week in the availability area,
And 2, could be considered as either anyone with -9 or more (or less if you know what I mean.. -9 to -37.5 or whatever) in a given week, or even anyone with more than 125% utilization in the utilization area for a given week.

I have been going through many different web sites and asking a lot of questions, but yesterday ordered some vba books off amazon, figuring it would be best if I actually learned this myself from the start… but in the mean time..

What I was thinking was a userform containing 3 drop down boxes
Select Organisation (this is because in the actual sheet there are 3 areas, belfast Chicago and stranane, each has a project area, and the availability, pto and utilization area.. but for this it wouldn’t be needed)
Select Week (where you could select the week you wanted to produce the information from)
Select Report (this would be to select either # of resources with >25% availability, or # of employees with >25% over allocation)
And a ‘create report’ button, so when you have chosen the criteria you want you click this and are given a list or a report of the information you asked for.

Basically two columns, resources and week (which would contain the hours of availability or over allocation) is all that’s needed as an output.

I was thinking this would be the best way to go about this, but I do not know the processes required so I do not know if there is maybe a better way.. any suggestions/ideas?

Does anyone think that, given a few days with the appropriate books and learning that this would be possible, I have plenty of computer knowledge, in unix and plsql/sql, abinitio and a few other things, though no vba skills… at all…

Could anyone start me off with the userform..?

Many Thanks
Neill