Jase,

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$278,$A280)*IF(E$6="S",4,8))

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$278,$A280)*IF(F$6="S",4,8))

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).Row) 'Watch these cells for holiday conflicts

Brad

Production-Plan-21-06-2013Q28172.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