DATABASE FORMULA

[becky]

I have a formula that I created in a worksheet that reads thus

=DSUM(DB,3,JAN)

The database is a range of cells from A6 to G36, the 3 represents the third
column in the database (or the field) and the JAN is the Criteria on another
sheet. These cells are C3 which equals the word DATE and C4 which has the
following formula

=AND('Expense 1'!A7>=Criteria!$A$4, 'Expense 1'!A7<=Criteria!$B$4)

Everytime I try to Copy the Sheet Expenses 1 the formula does not work. I
get all zeros where the =DSUM formula should be calculating.

Does anyone know how I can get these formulas to work when I Copy the Sheet
and create a new one? I need 25 Sheets that will all do the same math.

Thanks
--
bb

[Debra Dalgleish]

Instead of using a workbook level range name (DB), you could use a sheet
level name:

Select the cells that you want to name
Choose Insert>Name>Define
In the Name box, type the sheet name, exclamation mark, and range name.
For example: 'Expense 1'!DB
Click Add, click Close

However, your JAN criteria range refers to the values on the Expense 1
sheet, so it won't work correctly for other sheets. On the Expense 2
sheet, you could use a DSUM formula:

=DSUM('Expense 2'!DB,3,FEB)

and in the criteria range use the formula:

=AND('Expense 2'!A7>=Criteria!$A$4, 'Expense 2'!A7<=Criteria!$B$4)

Or, instead of a DSUM formula, use SUMPRODUCT:

=SUMPRODUCT(--(A7:A36>=Criteria!A4),
--('Expense 1'!A7:A36<=Criteria!B4), --('Expense 1'!C7:C36))

becky wrote:
> I have a formula that I created in a worksheet that reads thus
>
> =DSUM(DB,3,JAN)
>
> The database is a range of cells from A6 to G36, the 3 represents the third
> column in the database (or the field) and the JAN is the Criteria on another
> sheet. These cells are C3 which equals the word DATE and C4 which has the
> following formula
>
> =AND('Expense 1'!A7>=Criteria!$A$4, 'Expense 1'!A7<=Criteria!$B$4)
>
> Everytime I try to Copy the Sheet Expenses 1 the formula does not work. I
> get all zeros where the =DSUM formula should be calculating.
>
> Does anyone know how I can get these formulas to work when I Copy the Sheet
> and create a new one? I need 25 Sheets that will all do the same math.
>
> Thanks


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html