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
gbentleyAsked:
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.

ZethCommented:
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
ZethCommented:
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
tureCommented:
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
0
tureCommented:
********************************************************************************
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.