Return 3D array?

[Dave P]

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

 

[Dave P]

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.

 

[Ashish Mathur]

Hi,

You cannot specify a 3D reference in the Name box. I suggest you use the
morefunc Excel addin and then use the sheetname formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Lori]

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)

 

[BlueJay]

Lori,

Thank you very much for the reply. I think this will work well.

Dave

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)

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.

 

[BlueJay]

Thank you very much for this information. I will explore the addin.

Dave

 

[BlueJay]

Lori!

This defined name and array formula reference to "Sheets" is fantastic!
Exactly what I needed: a way to keep the list automatically updated in the
event that a tab name is changed.
Thanks again!

Dave

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)

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.