DSUM in Excel 97
Posted by Dana M on May 17, 2001 11:37 AM
When I put a sheet together in Excel 2000 using a dsum, it worked just fine. When I do it in Excel 97, the dsum will not work. It appears not to like formulas as criteria, however, it is necessary to have formulas! Anyone?
Posted by Mark W. on May 17, 2001 12:28 PM
DSUM() works just fine in Excel 97. Care to more
fully describe your configuration and symptoms?
Posted by lenze on May 17, 2001 1:48 PM
I agree with Mark. Formulas work fine with DSUM in Excel 97. Have you checked to make sure the functions in your formulas are available in Excel 97. If the functions are part of the Analysis Tool Pack Add-In, It must be installed for hem to be available.
Posted by Dana M on May 18, 2001 6:09 AM
well, i am using the dsum formula in combination with a pivot table report. there are 3 pivot points....month, day, and year. i made this into one cell - "date" that changes as the pivots change. if the user selects the date to be may 15, 2001, i want the dsum to sum the database from may 1 to may 15. the way i have it set up works in excel 2000 but not in 97. my dsum IS:
dsum(database, 11, K23:M24) with K23:M24 being:
K23 = city
K24 = chicago
L24 = date in database>=date value from pivot - day + 1
M24 = date in database<=date value from pivot
like i said, i am getting the correct results in 2000 but when i open it in 97, i get no results.
Posted by Mark W. on May 18, 2001 6:39 AM
Dana, exactly what are the formulas in L24 and M24?
Posted by Dana M on May 18, 2001 7:14 AM
L24 = =Data!H2>=VALUE(Flash!I23)
M24 = =Data!H2<=VALUE(Flash!I24)
the "H" column (as in H2) contains the dates in my database. I24 is the date from the pivot table. I23 is the first date of the month chosen in I24.
Posted by Mark W. on May 18, 2001 7:19 AM
Ah!
Whenever you use a relative cell reference in
a criteria, Excel will increment the cell address
as it steps through the database. Use $I$23 and
$I$24 instead!
Posted by Dana M on May 18, 2001 9:44 AM
Re: Ah!
well it sounded good anyway! it didn't work. it works fine in excel 2000 but as soon as i try to change the date on the pivot table in excel 97, everything goes crazy still. Whenever you use a relative cell reference in
Posted by Mark W. on May 18, 2001 10:35 AM
Re: Ah!
> everything goes crazy
In what way? Are your PivotTable items shifting
around? well it sounded good anyway! it didn't work. it works fine in excel 2000 but as soon as i try to change the date on the pivot table in excel 97, everything goes crazy still. : Whenever you use a relative cell reference in
Posted by Dana M on May 21, 2001 6:52 AM
Re: Ah!
The formula that has the dsum returns no value in Excel 97. When I open it in 2000, I get the right number. When I open it in 97, I just get "$ - " Nothing.
