R
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
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