ExcelBanter - View Single Post - Relative worksheet reference in 3-D formulas?

wrote...
....
I defined a name as SHEETARRAY = GET.WORKBOOK(1)

GET.WORK(1) returns a array of fullpaths to each worksheet in order.

and a second name of COPYDATA as

=INDIRECT("'"&MID(INDEX(SHEETARRAY,2),FIND("]",INDEX(SHEETARRAY,2))+1,128)
&"'!"&ADDRESS(ROW(),COLUMN()))

Works like a champ. I was pleasantly surprised INDEX() worked with
the
array like that. I put =COPYDATA in wherever needed.

Be VERY CAREFUL using this. If you're running XL97 or XL2K, copying
ranges on one worksheet containing references to such names and pasting
into other worksheets will CRASH Excel, so full data loss since last
save.

Also, INDIRECT(x&ADDRESS(ROW(),COLUMN())) is wasteful. Try
INDIRECT(x&"RC",0). Anything you can do with INDIRECT(..ADDRESS()) can
be done without the ADDRESS call using R1C1-style addressing.

Finally, there's no need to remove the workbook pathname portion of the
entries in SHEETARRAY. Excel ignores them if they point into the same
workbook. So you could just use
=INDIRECT("'"&INDEX(SHEETARRAY,2)&"'!RC",0)