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 B 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 http://www.gnome.org/projects/gnumeric/doc/sect-data-types.html, 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 mini-tables?
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. Regards, Jean
Attachment:
signature.asc
Description: Ceci est une partie de message numériquement signée