Indexing across tabs

[mf]

is there anyway to index across multiple tabs in Excel?
The Lotus function i want to convert is @INDEX
($01:$c$21..$30:$v$21,C$71,0,$p35) where the "01"
and "30" are the first and last tabs of the groups i want
to index through. Ive tried =INDEX('01'!$C$21:'30'!
$V$21,C$71,0,$P35) in Excel with no luck - a "VALUE"
error. Is this function even possible in Excel!?
thanks...

 

[ivano]

-----Original Message-----
is there anyway to index across multiple tabs in Excel?
The Lotus function i want to convert is @INDEX
($01:$c$21..$30:$v$21,C$71,0,$p35) where the "01"
and "30" are the first and last tabs of the groups i want
to index through. Ive tried =INDEX('01'!$C$21:'30'!
$V$21,C$71,0,$P35) in Excel with no luck - a "VALUE"
error. Is this function even possible in Excel!?
thanks...

.
The formulas INDEX mode reference take as argument

rif,row,column,area and you can insert multiple areas
in this. ES:

=INDEX((A1:C6,A8:C11,B3:B25),2,2,2) return second row,
second colum for ref A8:C11.
ivano

 

[Harlan Grove]

The formulas INDEX mode reference take as argument
rif,row,column,area and you can insert multiple areas
in this. ES:

=INDEX((A1:C6,A8:C11,B3:B25),2,2,2) return second row,
second colum for ref A8:C11.

Multiple area *RANGES*, but Excel's ranges, even the multiple area ones, are
restricted to a single worksheet. Something like

=INDEX((Sheet1!A1:B10,Sheet2!A1:B10,Sheet3!A1:B10),2,3,2)

will return #VALUE! errors.

Excel equivalents to 123's 3D @INDEX require either add-in functions or
user-defined functions written in VBA. The most general add-in is Laurent
Longre's MOREFUN.XLL, which is available at http://longre.free.fr/english/ .
Once installed and loaded as an add-in using the Add-In manager from the
Tools > Add-ins... menu command, it provides a function named THREED, which
converts 3D blocks into 2D arrays by stacking the ranges from each worksheet
vertically, e.g.,

Sheet1!A1:B4
111 112
121 122
131 132
141 142

Sheet2!A1:B4
211 212
221 222
231 232
241 242

Sheet3!A1:B4
311 312
321 322
331 332
341 342

then THREED(Sheet1:Sheet3!A1:B4) returns the array

111 112
121 122
131 132
141 142
211 212
221 222
231 232
241 242
311 312
321 322
331 332
341 342

So you can use THREED for 3D indexing like so:

123: @INDEX(Sheet1:A1..Sheet3:B4,c-1,r-1,s-1) [0-based indexing]
Excel: INDEX(THREED(Sheet1:Sheet3!A1:B4),r+4*(s-1),c) [1-based indexing]

Note that the 3 in the Excel formula is ALMOST unavoidable since Excel
provides no mechanism for counting worksheets in a 3D reference. However,
you can eliminate the hadcoding with the longer/uglier/crude

INDEX(THREED(Sheet1:Sheet3!A1:B4),r+ROWS(Sheet1!A1:B4))*s,c)

As for UDFs, they've been discussed before.

http://groups.google.com/[email protected]

(if this wraps, try http://makeashorterlink.com/?T21A21F35)

One last possibility if you don't mind LONG formulas. Try

INDEX(INDIRECT(TEXT(INDEX(ROW(INDIRECT("1:30")),$P35+1),"'00'!")&
CELL("Address",'01'!$C$21)&":"&CELL("Address",'30'!$V$21)),1,C$71+1)

The '+1's contemplate the transition from 123's 0-based to Excel's 1-based
indexing. Using CELL("Address",.) allows the formulas to change when you
insert or delete columns.

The MOREFUNC.XLL/THREED solution is the most robust, but it does require
installing the add-in.