Hi Guys

I know you can help me on this

I have a planning sheet that lists each job situated in certain bays / areas. Each process is allocated a set amount of hours and against them, on the plan, is a letter signifying the type of resource required, eg, E for electrician so on so forth.

Each day the plant works 8 hours so each square on the plan is 8 hours worth, Now the first thing you'll notcie is that the planned hours sometimes does not relate wholly to the amount of shaded squares. This im not bothered as I only want to use the shaded squares to get my result.

So, in row E is the actual dates per day off which another piece of code (Kindly provided by another expert on here) that deals with holidays etc.

For this exercise, could someone help me with a formula, array, or code that will look in each job section, just out to December this year will be fine, and say if today is 1st July, count how many shaded lettered squares are in this section, under previous dates, and multiply them by 8 to give me hours / days in arrears.

Basically, we havent had a job go out on time and its the trigger to justify spending the overtime versus customer satisfaction so its quite cruicial.

I tried a few ways but to avail so any help I would very much appreciate

I have attached the actual sheet for your viewing pleasure ;o))

Thanks

J

Production-Plan-21-06-2013.xlsm
I found this question confusing as to what you wanted. So I guessed that you want to calculate the number of hours by trade represented by letters representing that trade occurring before the date in row 6 (i.e. in columns to the left of). This formula assumes that letters will be erased when tasks are completed.

If I have completely missed the boat, could you please give a sample calculation (with correct answer) showing you want for one trade and date?

Assuming I had guessed correctly, I copied the trade initials and names to A280:B290. I then put the following formula in F280 and copied across and down:

=SUM(E280,COUNTIF(E$7:E$27

This formula keeps a running total of the number of backlog hours (8 on weekdays, 4 on Saturday) by trade and date.

Hours for the current day are not included in the backlog. If you want current day hours included in the backlog, change the formula to:

=SUM(E280,COUNTIF(F$7:F$27

I also had to make a change to the Worksheet_Change sub because it kept triggering when I was creating the backlog formulas. The statement setting the range for targ should be changed to:

Set targ = Range("F7:FX" & Range("B7").End(xlDown).Ro

Brad

Production-Plan-21-06-2013Q28172.xlsm