You can't use INDEX with multiple sheet references - 3D formula
functionality
is very limited in Excel.
If you just want a list of worksheets you can just enter directly into the
immediate window [Alt+F11 Ctrl+G]:
for each s in activeworkbook.Sheets: _
activecell.Offset(1,0).Select: _
activecell.Value = s.name: _
next s
(selecting the last line and pressing enter to run it).
If you need a dynamic array of worksheet names you could define the name
"Sheets" to refer to:
=TRANSPOSE(GET.WORKBOOK(1))&T(NOW())
and then select the cells to fill and CTRL+SHIFT+ENTER:
=MID(Sheets,FIND("]",Sheets)+1,255)
Dave P said:
My appologies.
Sent to soon.
I'll finish here.
Greetings,
My goal is to return a list of worksheet names on a sheet called
"Summary".
I have 30+ sheets. The first is Named "First" and the last is Named
"Last".
In A1 of each sheet is the formula to return the sheet name _
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
I've created a 3D reference called "SheetNames" =First:Last!$A$1
The formula =INDEX(SheetNames,1) returns #Value!
How can I return a 3D array as I would a 2D array?
Thanks.