Re: Using dsum across sheets

Le dim 25/01/2004 à 19:01, Mike Perry a écrit :
Thus spake Jean Br?fort (jean brefort ac-dijon fr):

Le dim 25/01/2004 ?? 01:44, Mike Perry a ??crit :
Is it possible to use the DSUM function on values from two different
sheets? For example, I am trying to write something like:

=DSUM(Timetable!A1:Timetable!F45, Timetable!F, A1,A17)

You have probably two mistakes in your formula. The first is that dsum
needs 3 arguments and not more. You must provide your critria in a
separate block as
1   Job #
2   16
Secondly, the second argument should be either the label of the column
("Time Delta") or the rank of the column in the database (this is what
is in the doc, but I couldn't make it work).
So your formula might be:
=DSUM(Timetable!A1:F5;"Time Delta";B1:B2)

=dsum(Timetable!A1:F45,"Time Delta",A1:A2) works, but the manual then is
a bit misleading. In example 5.10 in, they
say you are to do  =SUM('Sheet 1'!A1:'Sheet 1'!A5) when referencing a range
from another sheet..

The correct formula here is =SUM('Sheet 1'!A1:!A5). The documentation is
wrong on that point.

Also, why is it that "Time Delta" doesn't have a sheet scope on it? What
if I happen to have a "Time Delta" field in the Jobtable (which I don't,
but this seems like it would produce ill-defined behavior then).

It is a constant, not a reference. If you want to use a reference, you
can use Timetable!F1 instead.

Also, do I really need to use a range for the third argument? I really
don't want to have to create 16+ (the actual number of Jobs is
unbounded) mini-tables with just a "Job #" and a number in it,
especially when I already number each job in the Jobtable sheet. Is
there a function or something I can do to avoid all these needless

This is how database functions work. I don't think you can avoid them.
It works the same in OpenCalc or Excel.

Example 5.7 of the manual (same page) says I am supposedly able to specify
combination of cells with commas, which is again misleading cause it
doesn't work in this case.

This is true for functions which accept only a range of cells as sole
argument. A1,B2,C4,C5,D6 is considered as a single argument. For
functions several arguments, it would not be possible to know where the
arguments start and end if each one is a list of comma separated cell
references. It would be possible if the separators were not the same but
it is unfortunately not the case.



Attachment: signature.asc
Description: Ceci est une partie de message numériquement signée

[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]