# 3D Index

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.

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!!
LVL 5
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi gbentlev!

If I got you right, you want to sum or otherwise use values across sheets and/or workbooks.

You can do fex sum values across sheets and/or workbooks with this syntax:

=SUM(sheetname1!cellreference1;sheetname2!cellreference2;...;sheetnamen!cellreferencen)

Or if you want to sum a range of cells in each sheet:
=SUM(sheetname1!cellreference1:cellreference2;sheetname2!cellreference1:cellreference2;...;sheetnamen!cellreferencex:cellreferencey)

sheetsname can even be a sheet in another workbook, if you use the proper XL-syntax to reference a sheet in another workbook. Fex:
=SUM('path[filenamne1.xls]sheetname1'!cellreference1;...;'path[filenamnen.xls]sheetnamen'!cellreferencen)

Hope that will help something
Have a nice day!
dfghjk

0
Commented:
gbentlev!

A little complement!

"cellreverence" can ofcourse be a named range instead of  cellreferences.

i.e if you want to sum the range A1:C5, you can name that range to "something" and just use "something" instead of cellreference!

dfghjk/Zeth
0
Commented:
gbentley,

Excel really isn't very good at handling 3D references. I have created this little function for you, which may be of some help.

(If you don't know how to enter this VBA code, please ask back.)

Use the function like this in a cell, to retrieve the value from the named range "testname", first sheet, third row, second column:

=INDEX3D("testname", 1, 3, 2)

Function Index3d(NameName As String, SheetNumber As Long, RowNumber As Long, ColumnNumber As Long) As Variant
'Make this function recalculate whenever the worksheet is recalculated
Application.Volatile

Dim NameRef As String
Dim NameOfFirstSheet As String
Dim ws As Worksheet

'Initially set return value to '#VALUE!'
Index3d = Error(xlErrValue)

'Get the reference of the name.
'Something like: =Sheet1:Sheet3!\$A\$1:\$E\$10
NameRef = Names(NameName).RefersTo

'Extract name of the first sheet from the named range
If InStr(NameRef, ":") Then
NameOfFirstSheet = Mid(NameRef, 2, InStr(NameRef, ":") - 2)
Else
Exit Function
End If

'Extract range address from the named range
If InStr(NameRef, "!") Then
RangeAddress = Mid(NameRef, InStr(NameRef, "!") + 1)
Else
Exit Function
End If

'Set ws to the sheet indicated by SheetNumber
On Error Resume Next
Set ws = Sheets(Sheets(NameOfFirstSheet).Index + (SheetNumber - 1))
If Err Then Exit Function
On Error GoTo 0

'Return the value
On Error Resume Next
If Err Then Exit Function
On Error GoTo 0
End Function

Ture Magnusson
0
Commented:
********************************************************************************
0

Experts Exchange Solution brought to you by