Return 3D array?

D

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
 
D

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.
 
A

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
 
L

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)
 
B

BlueJay

Lori,

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

Dave

Lori said:
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.
 
B

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


Lori said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top