Surely the INDIRECT function will simply return a range reference (which is
3-D in thiscase), which is then passed to SUM for evaluation?
You're making an unfounded assumption. In Excel, no matter what you may
prefer to believe, ranges are *EXCLUSIVELY* 2-D, i.e., they must fit within
a single worksheet. Complicating matters, Range objects can be collections
of single area (rectangular blocks of cells) ranges, but they must all be in
the same worksheet.
This is one of those things you have to learn empirically. Typical for
Microsoft, XL2K (at least) online help includes 3-D 'ranges' in those things
that could be stored in Range object. Good luck trying to construct one in
VBA! Can't be done. Also, Range objects have no properties that reflect the
third (worksheet) dimension.
So, Range objects can only be 2-D, so 3-D referents can't be Range objects.
Since INDIRECT can only return references to Range objects or #REF! errors,
INDIRECT can't return 3-D references.
I entered the range formula in a cell:
{=('20030731:20030831'!E7)}
If you then evaluate this (highlight and press F9) it does not return an
array.
No, because Excel provides no true support for 'worksheet-axis' *ordered*
dimensionality. [Digression: there's one and only one exception to this of
which I'm aware - the NPV function accepts 3-D references as it's second or
subsequent argument, and it does iterate through 3-D references in a given
order. This is another instance in which online help is (to be charitable)
incomplete.] That is, Excel provides no means to convert 3-D references into
anything at could be entered into worksheet cells. The only thing Excel can
do/does with 3-D references is iterate through all cells in them in a
*limited* number of functions that return scalar (single value) results,
such as SUM, AVERAGE, etc. [NPV can return arrays, but only if it's first
argument, interest rate(s), is an array.]
This makes the behaviour of the INDIRECT / SUM interaction entirely
understandable, but how does SUM correctly interpret the range (array) under
normal circumstances and sum the individual elements?
Because 3-D references are *neither* ranges *nor* arrays. 3-D references are
syntactic bandages Microsoft introduced in Excel 4 in response to Lotus
introducing TRUE 3-D functionality in 123 Release 3 back in 1989. True to
Microsoft form, they didn't want to invest much in actual development and
programming, so they didn't incorporate true 3-D functionality into Excel.
Instead, they created a new syntactic entity that functioned somewhat like
data consolidation, and you should note that 3-D references can only be used
in data consolidation-like functions.
Much has been written about this in the microsoft.public.excel.* and
comp.apps.spreadsheets newsgroups over the years. Check the Google Groups
archive if you're curious. Aside from that, get used to the fact that Excel
can't do what you want it to do *except* by using user-defined functions.
Something like
Function si3d(tul As Variant, blr As Variant) As Variant
Dim ra As String, si As Long, i As Long, wsc As Sheets
On Error Resume Next
If TypeName(tul) = "String" Then Set tul = Range(tul)
If Err.Number <> 0 Then
si3d = CVErr(xlErrRef)
Exit Function
End If
If TypeName(blr) = "String" Then Set blr = Range(blr)
If Err.Number <> 0 Then
si3d = CVErr(xlErrRef)
Exit Function
End If
On Error GoTo 0
If Not tul.Parent.Parent Is blr.Parent.Parent Then
si3d = CVErr(xlErrRef)
Exit Function
End If
'the + 0.5 prevents si from being 0
si = Sgn(blr.Parent.Index - tul.Parent.Index + 0.5)
ra = Range(tul.Address(0, 0), blr.Address(0, 0)).Address(0, 0)
Set wsc = tul.Parent.Parent.Worksheets
For i = tul.Parent.Index To blr.Parent.Index Step si
si3d = si3d + Application.Sum(wsc(i).Range(ra))
Next i
End Function
In your example formula, use this udf as
=si3d(INDIRECT("'20030731'!E7"),"'20030831'!E7")
The two arguments to si3d are taken to be either range references to or
string range addresses of the possibly 2-D ranges containing opposite
corners of the 3-D reference over which to sum all cell values.
