asked on

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

Zeth

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

Zeth

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

ture

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 RangeAddress 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
Index3d = ws.Range(RangeAddress).Cells(RowNumber, ColumnNumber).Value
If Err Then Exit Function
On Error GoTo 0
End Function

Ture Magnusson
Karlstad, Sweden

ASKER CERTIFIED SOLUTION

ture

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial