Index and 3D ranges
Gordon Bentley
files, I use the @Index() function extensively. It allows you to index into
a 3 dimensional range(ie several sheets) by a specified number of sheets,
rows and columns. I find that the Excel equivalent, doesn't work with 3D
ranges.
Can somebody suggest a replacement formula that will return the value a
specified number of sheets,rows,columns into a 3D range. The numbers will be
in other cells.
ie
A1 = 5
B1 = 10
C1 = 4
@INDEX(RANGENAME,A1,B1,C1) would return the value 5 sheets in, 10 rows down,
and 4 columns across form the origin of the 3D range named RANGENAME.
Help!!
Gordon
mailto:gben...@birdcameron.com.au
Harlan Grove
<gordon_...@hotmail.com> writes:
>I am translating several spreadsheets from Lotus 123 to Excel. In the Lotus
>files, I use the @Index() function extensively. It allows you to index into
>a 3 dimensional range(ie several sheets) by a specified number of sheets,
>rows and columns. I find that the Excel equivalent, doesn't work with 3D
>ranges.
...
>A1 = 5
>B1 = 10
>C1 = 4
>
>@INDEX(RANGENAME,A1,B1,C1) would return the value 5 sheets in,
>10 rows down, and 4 columns across form the origin of the 3D range named
>RANGENAME.
If the @INDEX above is supposed to show how 123 does it, swap A1 and C1 since
the 123 function is @INDEX(Range,ColumnOffset,RowOffset,SheetOffset).
First, when in Excel use 1-based indexing, so (ignoring the 3rd dimension for
the moment) @INDEX(Range,4,10) in 123 would become =INDEX(Range,5,11) in Excel.
As for 3D indexing, you either need a VBA user-defined function (UDF) or
Laurent Longre's MOREFUNC.XLL add-in, which is freely available at
http://perso.wanadoo.fr/longre/excel/pages/Downloads.htm#Morefunc.xll
First the UDF. This would need to be put in a general VBA module rather than as
code in any worksheet or ThisWorkbook. This is what I use - it's S L O W
.
'alpha and omega are two opposite vertices of a 3D range - I haven't come up
with
'any way to pass a 3D range to a VBA UDF
Function Index3D(alpha As Range, omega As Range, _
ri As Long, ci As Long, si As Long) As Variant
Dim t As Long, d(1 To 2, 1 To 3) As Long
d(1, 1) = Application.WorksheetFunction.Min(alpha.Cells(1, 1).Row, _
omega.Cells(1, 1).Row) - 1
d(1, 2) = Application.WorksheetFunction.Min(alpha.Cells(1, 1).Column, _
omega.Cells(1, 1).Column) - 1
d(1, 3) = Application.WorksheetFunction.Min(alpha.Worksheet.Index, _
omega.Worksheet.Index) - 1
d(2, 1) = Application.WorksheetFunction.Max(alpha.Cells(1, 1).Row, _
omega.Cells(1, 1).Row) - d(1, 1)
d(2, 2) = Application.WorksheetFunction.Max(alpha.Cells(1, 1).Column, _
omega.Cells(1, 1).Column) - d(1, 2)
d(2, 3) = Application.WorksheetFunction.Max(alpha.Worksheet.Index, _
omega.Worksheet.Index) - d(1, 3)
If 0 < ri And ri <= d(2, 1) And _
0 < ci And ci <= d(2, 2) And _
0 < si And si <= d(2, 3) Then
Index3D = Worksheets(d(1, 3) + si).Cells(d(1, 1) + ri, d(1, 2) + ci)
Else
Index3D = CVErr(xlErrValue)
End If
End Function
The alternative using Laurent Longre's MOREFUNC.XLL add-in is, for example,
=INDEX(THREED(Sheet1:Sheet20!A1:N500),(A1-1)*500+B1,C1)
assuming 1-based indexing. The THREED function takes a 3D reference and
converts it into a 2D array like so:
Sheet1
---------------
1_1_1 1_2_1 1_3_1
2_1_1 2_2_1 2_3_1
Sheet2
---------------
1_1_2 1_2_2 1_3_2
2_1_2 2_2_2 2_3_2
:
SheetN
---------------
1_1_N 1_2_N 1_3_N
2_1_N 2_2_N 2_3_N
=THREED(Sheet1:SheetN!A1:C2) gives the 2D array
1_1_1 1_2_1 1_3_1
2_1_1 2_2_1 2_3_1
1_1_2 1_2_2 1_3_2
2_1_2 2_2_2 2_3_2
:
1_1_N 1_2_N 1_3_N
2_1_N 2_2_N 2_3_N
So the (A1-1)*500 term in the =INDEX(THREED(...)...) formula above multiplies
the sheet offset by the number of rows in each sheet to give a row offset into
the THREED result array for a given sheet index.
Neither are particularly close to 123 3D @INDEX function, but Excel isn't
really a 3D spreadsheet. Workarounds are necessary.