with Formula No VBA how to get the Sheet Names?

[jamiguel77]

Hi how to get the sheet names with formula, (ideally store in a combobox)

i am here get the actual sheet name:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

or

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

but get the actual sheet name, how to get the (30 sheets names)

thanks..

 

[rlv01]

i am here get the actual sheet name

When I use this formula

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

I get the 'actual' sheet name. You might need to explain more.

 

[Dave Patton]

1. Create a named range (I used SheetList) using the formula =SUBSTITUTE(GET.WORKBOOK(1),"[" & GET.WORKBOOK(16) & "]","") in the Refers to text box
2. Enter the following formula into the first cell (I used cell A2) where you want to the results to start =INDEX(SheetList,0,ROW()-1) Note the column_num argument refers to the sheet number in the workbook so for completeness it needs to start at 1 (which since I started in cell A2 I subtracted 1).
3. Copy the formula down for as many sheets there are in your workbook (a #REF! will appear if the column_number argument is higher than the number of sheets in the workbook).

T10_1712b.xlsm
	A
1
2	1a
3	1b
4	1c
5	1d
1a

Cell Formulas
Range	Formula
A2:A5	A2	=INDEX(SheetList,0,ROW()-1)