Add to a cell if a row has a date and a column has a department

[Craigm]

From another worksheet I want to total miles driven by department. A pivot table won't work for me here. I need to code this. A sample worksheet is attached.

Column 1 is departments (not sorted, one row for each driver). Columns 8 through 18 hold miles driven each month by each driver. Every driver gets one row, starting in row 8.

Row 5 holds the date of the last day of the quarter, there are 3 quarters of data and the number of columns grows by one each month. The first quarter end date is in Cells(5, 9). Currently the last quarters date is in Cells(5, 18). So above each coulmn of monthly of miles driven is the quarter end date in row 5 of the same column.

Rows 8 through 500 hold miles driven by each driver. Each row holds the 9 quarters of miles driven by each driver. The first available monthly miles driven data is cell(8,9). The last monthly miles driven for each driver is (8, 18)

From another worksheet in the same workbook I want to total miles driven by department.

I am in for..next, select..case hell and the gray cells have died!

Thanks for your help! Craigm

[Craigm]

Here is the attachment.

[Toppers]

Craig,
Attachments not allowed so can you post to
[email protected].

Your post seems to suggest only 11 months of data Cols 8 to 18) but no doubt
your attachment will clarify all.

"Craigm" wrote:

>
> From another worksheet I want to total miles driven by department. A
> pivot table won't work for me here. I need to code this. A sample
> worksheet is attached.
>
> Column 1 is departments (not sorted, one row for each driver). Columns
> 8 through 18 hold miles driven each month by each driver. Every driver
> gets one row, starting in row 8.
>
> Row 5 holds the date of the last day of the quarter, there are 3
> quarters of data and the number of columns grows by one each month.
> The first quarter end date is in Cells(5, 9). Currently the last
> quarters date is in Cells(5, 18). So above each coulmn of monthly of
> miles driven is the quarter end date in row 5 of the same column.
>
> Rows 8 through 500 hold miles driven by each driver. Each row holds
> the 9 quarters of miles driven by each driver. The first available
> monthly miles driven data is cell(8,9). The last monthly miles driven
> for each driver is (8, 18)
>
> From another worksheet in the same workbook I want to total miles
> driven by department.
>
> I am in for..next, select..case hell and the gray cells have died!
>
> Thanks for your help! Craigm
>
>
> --
> Craigm
> ------------------------------------------------------------------------
> Craigm's Profile: http://www.excelforum.com/member.php...o&userid=24381
> View this thread: http://www.excelforum.com/showthread...hreadid=393434
>
>