• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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!!
0
gbentley
Asked:
gbentley
  • 2
  • 2
1 Solution
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now