Prom with the DSUM function

[oteixeira]

Hello
I'm having a problem with the DSUM function. As everybody knows, parameters for this function are DSUM(database, field, criteria). I have no problems with "field" and "criteria". My worksheet has a lot of defined names ranges (which i stored in cells) and i wanted to use them to replace the "database" parameter each time i choose a different department from a list. For example i have range B6:F96 named as "area_1". If i write the formula using =DSUM(area1, field, criteria), it works fine. However, if a store the name "area_1" into cell A1 and change the formula to =DSUM(A1, field, criteria) i get #VALUE. The parameter criteria must be a cell with the name of the database column and at lest one cell above with the filter to be applied. For these parameter i can use a named range. Will it be possible that i cannot use this technique for the database parameter?
Any help would be very much appreciated.

[Marcelo]

Hi Teixeira,

try to use an indirect function as:

DSUM(INDIRECT(A1),FIELD,CRITERIA)

Hope this helps
Regards from Brazil
Marcelo

"oteixeira" escreveu:

>
> Hello
> I'm having a problem with the DSUM function. As everybody knows,
> parameters for this function are DSUM(database, field, criteria). I
> have no problems with "field" and "criteria". My worksheet has a lot of
> defined names ranges (which i stored in cells) and i wanted to use them
> to replace the "database" parameter each time i choose a different
> department from a list. For example i have range B6:F96 named as
> "area_1". If i write the formula using =DSUM(area1, field, criteria),
> it works fine. However, if a store the name "area_1" into cell A1 and
> change the formula to =DSUM(A1, field, criteria) i get #VALUE. The
> parameter criteria must be a cell with the name of the database column
> and at lest one cell above with the filter to be applied. For these
> parameter i can use a named range. Will it be possible that i cannot
> use this technique for the database parameter?
> Any help would be very much appreciated.
>
>
> --
> oteixeira
> ------------------------------------------------------------------------
> oteixeira's Profile: http://www.excelforum.com/member.php...o&userid=35320
> View this thread: http://www.excelforum.com/showthread...hreadid=551007
>
>

[oteixeira]

Hi Marcelo,
That was a really good one! Thanks a lot!!
Greetings from Portugal

[Marcelo]

Obrigado pelo retorno
saudações
Marcelo

"oteixeira" escreveu:

>
> Hi Marcelo,
> That was a really good one! Thanks a lot!!
> Greetings from Portugal
>
>
> --
> oteixeira
> ------------------------------------------------------------------------
> oteixeira's Profile: http://www.excelforum.com/member.php...o&userid=35320
> View this thread: http://www.excelforum.com/showthread...hreadid=551007
>
>