printing a worksheet name in a cell

[law]

On my spreadsheet, cell A1 has the following formula: =cell("filename). The result is:

\Admin2-ladontemp[Copy of 0904-CAPITAL AVIATION.xls]Summary Sheet

How would I get the phrase "Summary Sheet", to appear in Cell A1? Also, is there a formula that can just print the name of a particular worksheet in a workbook?

Thanks for all of your help

Law

 

[Ricky Morris]

Try:

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

 

[Asala42]

Here's a couple ways.

1 variation of the find "]" approach:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))


A UDF using the formula: =SheetName(A1)

Function SheetName(Cell As Range)
sheetname = Cell.Parent.Name
End Function

 

[law]

On 2002-09-23 09:56, Asala42 wrote:
Here's a couple ways.

1 variation of the find "]" approach:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))


A UDF using the formula: =SheetName(A1)

Function SheetName(Cell As Range)
sheetname = Cell.Parent.Name
End Function


Thanks Asala42 and Ricky. Both formulas do the trick!!!!