getting the worksheet name in a cell

[derohanes]

Is there a command that retrieves the worksheet name and puts it into a cell.

[Catalin.B]

To do that, there is a way: Define a name, like SheetName, and place in the "Refers to:" field, this formula:
=GET.CELL(62;Sheet2!$G$26) (if you want another sheet, change the reference in red)
Then, in any cell in any sheet, you will get the sheet2 name with:
=RIGHT(SheetName;LEN(SheetName)-FIND("]";SheetName))
You can simply use:
=SheetName
but you will get the sheet name precedeed by the workbook name...
For a list of information that can be obtained from Get.Cell function, (the function is available only in defined names, or in VB), you can check this:
http://www.mrexcel.com/forum/excel-q...arguments.html

Or, another way, is to use Cell function, available in sheet functions:
=RIGHT(CELL("filename";J16);LEN(CELL("filename";J1 6))-FIND("]";CELL("filename";J16)))
The =CELL("filename";J16) formula extracts the full path of the reference...

[macropod]

Just in case you're wondering, the 'J16' in Catalin's formula is of no consequence. The following are equally valid:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1) )-FIND("]",CELL("filename",A1)))
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A 1))- FIND("]",CELL("filename",A1)))

Note: the ';' in Catalin's formula assumes a non-English Windows regional setting. The ',' in mine assumes an English Windows regional setting.

[Catalin.B]

Absolutely right Paul, with just a minor correction: there are english regional settings with ';' as list separator...

[macropod]

Originally Posted by Catalin.B

Absolutely right Paul, with just a minor correction: there are non-english regional settings with ';' as list separator, like French and many others...

And these are related to your Windows regional settings, n'est pas?

PS: The formula solutions only work with a saved workbook.

[Catalin.B]

My mistake, i meant:
"there are english regional settings with ';' as list separator..."
By the way, i'm not a fluent french speaker :d

[derohanes]

Thanks all. Where is a list of the reference fields for the info_type "filename"?

[Catalin.B]

When you start typing: =Cell and double click on CELL from the list of functions, this is what you see:

[derohanes]

Thanks Caitlin, but those are the info_types. I was asking for the references for the info_type "filename". In this case, A1

[macropod]

As I said in my first post on this topic, with "filename" the cell reference (A1) is of no consequence. You could use any valid cell address and the result would be the same. That's because every cell on a worsheet belongs to the same worksheet ...