Dynamic DSUM?

[Don]

I have a large table with approx. 1500 rows and 34 columns. It
compiles and summarizes payroll hours, by day of the week, type of
hour, and location.

Each row contains a different unit identifier code.

Data is stored in 7 separate daily payroll files, one day on each tab
of the workbook. We have complex tab names such as SA, SU, MO, TU,
WE, TH, FR for these files. As you see, real complex.

Anyway, the dsum formula is pretty straight forward as shown below.

=DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)

Here comes the problem. I need a way to make the DSUM formula dynamic
or the criteria cells to be dynamic so the unit identifier code is
brought into play. The daily payroll data files are between 5,000 and
50,000 lines and the same unit identifier will show up many times.

As the formula is written above, cell O2 is where the unit identifier
is shown. Although I realize I could just repeat M1:O2 1500 times
simply changing the unit identifier each time, it seems as though
there should be an easier way.

I am currently using SUMPRODUCT to get our data but it is very slow to
compile and very processor intensive. DSUM seems to work much quicker
and appears to be as accurate so it looks like an alternative.

Your assistance is greatly appreciated.

Don

 

[Homey]

Table command on Data menu may help you. It can plug list of labels into
criteria cell and make table of answrs.


|I have a large table with approx. 1500 rows and 34 columns. It
| compiles and summarizes payroll hours, by day of the week, type of
| hour, and location.
|
| Each row contains a different unit identifier code.
|
| Data is stored in 7 separate daily payroll files, one day on each tab
| of the workbook. We have complex tab names such as SA, SU, MO, TU,
| WE, TH, FR for these files. As you see, real complex.
|
| Anyway, the dsum formula is pretty straight forward as shown below.
|
| =DSUM(SU!$A$1:$J$10000,"OT",SU!$M$1:$O$2)
|
| Here comes the problem. I need a way to make the DSUM formula dynamic
| or the criteria cells to be dynamic so the unit identifier code is
| brought into play. The daily payroll data files are between 5,000 and
| 50,000 lines and the same unit identifier will show up many times.
|
| As the formula is written above, cell O2 is where the unit identifier
| is shown. Although I realize I could just repeat M1:O2 1500 times
| simply changing the unit identifier each time, it seems as though
| there should be an easier way.
|
| I am currently using SUMPRODUCT to get our data but it is very slow to
| compile and very processor intensive. DSUM seems to work much quicker
| and appears to be as accurate so it looks like an alternative.
|
| Your assistance is greatly appreciated.
|
| Don
|