Multiple sheet name references

I have 30 sheets in one workbook. In Sheet1, how can I use a formula to return sheet names of 开发者_JAVA百科the other 29 sheets within the same workbook? My preference is not to use udf or vba.

This is the formula I am using to return the name of the active worksheet:

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

I modified the formula to this:

=MID(CELL("filename",'Sheet 1'!A1),FIND("]",CELL("filename",'Sheet 1'!A1))+1,25)

but when I use this formula, I get a message box titled "Update Values: Sheet 1" - essentially prompting me to select the workbook. Once I select the workbook, I am prompted to select the sheet. Once I do that, the formula returns #N/A. I tried resaving the workbook, but same result.

You could use an obscure XL4 function, GET.WORKBOOK to return a list of the sheets in the active workbook instead as follows:

Define a range name called "WorksheetNameList" using the formula:

=GET.WORKBOOK(1)

Then, in your Sheet1 use the formula in cell A1:

=MID(INDEX(WorksheetNameList,ROW(A1)),FIND("]",INDEX(WorksheetNameList,ROW(A1)))+1,32)

and then drag the formula down the rows until you hit row 30 (or you obtain a #REF!).

I would not normally recommend using XL4 functions since they could disappear at any time with new Excel releases, but they are used here based on the restriction of your question.

If you'd like more information on what else GET.WORKBOOK can return, please have a look at the help file which is located at http://www.microsoft.com/downloads/details.aspx?FamilyID=c09bf7f7-d30e-4ce9-8930-5d03748ca5cd