Picking up the name of the sheet

[gilbert]

Any idea what formula to use if I want a cell in the excel sheet to show the name of the worksheet?

[Dunc3142]

I looked for 4 years before I found this.

[ChemistB]

Sounds like you want to use the CELL("filename") Function. Here's a list of the variations you might want;
For any of these to work the workbook must be saved first.

Please Login or Register  to view this content.

Let us know if it works.
ChemistB

[gilbert]

Sounds like you want to use the CELL("filename") Function. Here's a list of the variations you might want;
For any of these to work the workbook must be saved first.

Please Login or Register  to view this content.

Let us know if it works.
ChemistB

Ya.. it works well with my worksheet.. but mind to explain a bit how does this formula works? I am using the last one where showing Worksheet name only. Wonder why reference is to cell A1?

[ChemistB]

Okay, starting with your second question. It references cell A1 simply to "ground" the formula within that worksheet. If that were not there, the formula would calculate based on the last sheet you made a change.

The formula

Please Login or Register  to view this content.

returns the entire path including workbook name and sheet name.

With

Please Login or Register  to view this content.

The MID function plucks a certain number of characters from a string with the format =MID(TEXT,Starting Number, Number of Characters).

The FIND function finds what position (#)the "]" is which appears right before your sheetname and then adds 1 (where your sheetname starts). 255 is an arbitrary number to cover the number of characters in your sheet name.

ChemistB

[Dunc3142]

Code remove as unwrapped

[Dunc3142]

New lines added.

[gilbert]

How about picking up certain part of the workbook filename, that is without showing the file extension - .xls?

[gilbert]

Got it now.....

=MID(LEFT(CELL("filename",A1), FIND("]", CELL("filename", A1))- 5), FIND("[",CELL("filename", A1))+1, 255)