referencing a 3D Named Range

[Robert H]

I am reviewing a workbook that has multiple spreadsheets of similar
data that are referenced on a ¡§Total Costs¡¨ sheet. Right now we use
some pretty cumbersome formulas on the Totals sheet that I want to
clean up. I am trying to use 3D references, actually 3D Named Ranges
to simplify. I am aware that the range must be the same on each sheet
and have established for that. I¡¦m testing in a much simplified
workbook for the moment just to work the basics out. The 3D range is
named ¡§Results¡¨ and spans four sheets named 2005, 2004, 2003 and 2002
the common range on each sheet is C3:F5.

The following works, so I know the range is set up correctly:
=SUM(Results)


I¡¦m trying to perform calculations based on specific locations within
the range using the INDEX function or some other referencing method.
Eventually I want to identify the locations using headings and the
Match function but for now I¡¦m entering the INDEX arguments directly.

The following
=SUM(INDEX(Results,3,2))
Returns a #Value error

To isolate the problem, I created a name for the same range on just
one sheet ¡§Results2005¡¨ and with the following formula; I get the
correct value returned:
=SUM(INDEX(Results2005,3,2))

I¡¦m getting the feeling that I may be up against a limitation of using
3D ranges. Before I move on to another approach I wanted to turn it
over to the experts. Any advice, recommendations, or condolences will
be appreciated ļ
Robert

 

[Bernie Deitrick]

Robert,

From Help, the functions that can be used with 3D ranges - see below. (From the help page "Refer to
the same cell or range on multiple sheets")

Generally, do NOT use separate sheets with similiar databases - it is MUCH better to use an
additional column in your database with, in your case, the year, then apply filters or use a pivot
table to show the subset of the data in which you are interested.

HTH,
Bernie
MS Excel MVP

SUM - adds numbers
AVERAGE - calculates average (arithmetic mean) of numbers
AVERAGEA - calculates average (arithmetic mean) of numbers; includes text and logicals
COUNT - counts cells that contain numbers
COUNTA - counts cells that are not empty
MAX - finds largest value in a set of values
MAXA - finds largest value in a set of values; includes text and logicals
MIN - finds smallest value in a set of values
MINA - finds smallest value in a set of values; includes text and logicals
PRODUCT - multiplies numbers
STDEV - calculates standard deviation based on a sample
STDEVA - calculates standard deviation based on a sample; includes text and logicals
STDEVP - calculates standard deviation of an entire population
STDEVPA - calculates standard deviation of an entire population; includes text and logicals
VAR - estimates variance based on a sample
VARA - estimates variance based on a sample; includes text and logicals
VARP - calculates variance for an entire population
VARPA - calculates variance for an entire population; includes text and logicals


I am reviewing a workbook that has multiple spreadsheets of similar
data that are referenced on a ¡§Total Costs¡¨ sheet. Right now we use
some pretty cumbersome formulas on the Totals sheet that I want to
clean up. I am trying to use 3D references, actually 3D Named Ranges
to simplify. I am aware that the range must be the same on each sheet
and have established for that. I¡¦m testing in a much simplified
workbook for the moment just to work the basics out. The 3D range is
named ¡§Results¡¨ and spans four sheets named 2005, 2004, 2003 and 2002
the common range on each sheet is C3:F5.

The following works, so I know the range is set up correctly:
=SUM(Results)


I¡¦m trying to perform calculations based on specific locations within
the range using the INDEX function or some other referencing method.
Eventually I want to identify the locations using headings and the
Match function but for now I¡¦m entering the INDEX arguments directly.

The following
=SUM(INDEX(Results,3,2))
Returns a #Value error

To isolate the problem, I created a name for the same range on just
one sheet ¡§Results2005¡¨ and with the following formula; I get the
correct value returned:
=SUM(INDEX(Results2005,3,2))

I¡¦m getting the feeling that I may be up against a limitation of using
3D ranges. Before I move on to another approach I wanted to turn it
over to the experts. Any advice, recommendations, or condolences will
be appreciated ļ
Robert

 

[Robert H]

Thanks Bernie, I was afraid of that

 

[Harlan Grove]

From Help, the functions that can be used with 3D ranges - see
below. (From the help page "Refer to the same cell or range on
multiple sheets") ....
SUM [...]
AVERAGE [...]
AVERAGEA [...]
COUNT [...]
COUNTA [...]
MAX [...]
MAXA [...]
MIN [...]
MINA [...]
PRODUCT [...]
STDEV [...]
STDEVA [...]
STDEVP [...]
STDEVPA [...]
VAR [...]
VARA [...]
VARP [...]
VARPA [...]

....

And if this is from Help, it's another fine example of long-standing
errors, in this case of omission, in Excel's help files. Both MEDIAN
and NPV (but *not* XNPV, at least up to XL2003) also accept multiple
3D references.

All built-in functions that accept a variable number of arguments
*except* MODE and CONCATENATE accept 3D references.