DSum with 3-D references
Paul Rogers
I can't get it to work,though it seems like it should.
For example I tried the formulae: =DSUM(Elect:Water!
$A$15:$Z$97,"9",Criteria!$D$17:$E$18)- but it wouldn't
calculate.
I would really appreciate any advice on this - rather than
having to do the two functions seperately.
Thanks
Paul
John
I think maybe the ":" would be causing the problem.
I'm using office 2k so i can't actually name a sheet
containing chars like ":".
The only other reason i can think why it isn't working is
if the criteria contain column titles that don't EXACTLY
match the column titles in your data.
John.
>.
>
Stephen Bye
"Guidelines for using 3-D references
· You can use 3-D references to refer to cells on other sheets, to define
names, and to create formulas by using the following functions: SUM,
AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV,
STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA.
· 3-D references cannot be used in array formulas.
· 3-D references cannot be used with the intersection operator (a single
space) or in formulas that use implicit intersection."
"Paul Rogers" <paul....@dhs.vic.gov.au> wrote in message
news:08c001c371f2$8d5d4470$a301...@phx.gbl...
Harlan Grove
>is "Elect:Water" the name of a sheet?
>I think maybe the ":" would be causing the problem.
The '3-D' part of the OP's subject line should have been a clue that the OP was
interested in 3D references, i.e., those that span multiple worksheets. The
colon operator is needed between worksheet names in 3D references.
If you don't know what 3D references are, you shouldn't respond to questions
concerning them.
--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.
Harlan Grove
>Can you use 3-D references in DSum?
No, and FWLIW, no other spreadsheet I'm aware of that provides DSUM equivalents
allows them to operate across multiple worksheets. Xess's CSUM may, but that's
getting a bit exotic.
>I would really appreciate any advice on this - rather than
>having to do the two functions seperately.
Several 3D variations on SUMIF have been posted in the past, but like SUMIF
they're restricted to a single criterion. Your formula appears to use two
criteria. If there were only two worksheets involved, a formula using two
separate DSUM calls added together would recalc MUCH FASTER than any VBA udf you
might try using. However, if you really want a 3D DSUM, you could try the udf
below (see http://www.cpearson.com/excel/codemods.htm also).
Function DSUM3D(tul As Range, blr As Range, f As Variant, c As Range) As Variant
Dim i As Long, k As Long, n As Long, ra As String
If Not tul.Parent.Parent Is blr.Parent.Parent Then
DSUM3D = CVErr(xlErrRef)
Exit Function
End If
k = tul.Parent.Index
n = blr.Parent.Index
If k > n Then
i = k
k = n
n = i
End If
ra = tul.Range(tul.Address, blr.Address).Address(0, 0, xlA1, 0)
With tul.Parent.Parent.Worksheets
For i = k To n
DSUM3D = DSUM3D + _
Application.WorksheetFunction.DSum(.Item(i).Range(ra), f, c)
Next i
End With
End Function