Dsum "#value!"

[DCSwearingen]

I have been trying to follow along with Chip Pearson's DSUM Tutorial and apply it to an application here at work.

It is located at http://www.cpearson.com/Newsletter/C...2007_10_08.htm

I am trying to sum the production totals for item codes between two dates. I eventually want the dates to be dynamicly set by a user input, but I cannot even get the DSUM to work using hard entered values. I have tried entering actual dates, the serial numbers for the dates and everything else I can think of.

I am enclosing a zip of a small sample of the worksheet, I have even used the same names Chip Pearson uses in his tutorial, just applying them to the ranges in this sample.

If anyone can tell me where I am going wrong, I would be most appreciative.

[daddylonglegs]

You have dynacrit defined as just cell A72, it needs to be A73:A74, also your column criteria needs to be the column number (4) or the column header as text, i.e.

"1404006"

so if you make A87 just 4 and redefine dynacrit then you can use

=DSUM(data,A87,dynacrit)

[DCSwearingen]

I was able to leave DynaCrit as a dynamic address inside the INDIRECT function. My problem was with the use of the cell address to return the field header (part number.) When I simply substituted COLUMN() with the header cell enclosed, everything worked.

So, if I insert

=DSUM(Data,COLUMN(),INDIRECT(DynaCrit))

into the same column as the part number code the DSUM works.

Now I just need to make the Dates dynamic. I can use

=">="&A14
="<="&A30

to select a date in the table to set the upper and lower date limits and the DSUM still works, so now all I need to do is set this up in the real workbook and give the user a couple of cells to use for the date ranges to query.

Thank you for turning me in the right direction. You guys are terrific!