workbook and sheet name via formula

liverlarson 

Read out the designation / name of the worksheet

Read out by formula

The focus of the formula solution is the CELL function (infotype; reference). In addition to the file name and the full path, the File Name infotype also returns the name of the worksheet. This is extracted using further functions so that only the sheet name is output at the end.

The result is achieved with these two formulas:
(formulas are from German Translate)
a) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; 255)

or

b) = PART (CELL ("filename"; A1); FIND ("]"; CELL ("filename"; A1)) + 1; LENGTH (CELL ("filename"; A1)) - FIND ("]"; CELL ("filename"; A1)))

2. Read out via VBA

The following VBA function reads out the sheet name via VBA and displays it in a cell. To do this, enter the following code in a code module.

Public Function sheetname () As String
Application.Volatile
sheetname = ActiveSheet.Name
End function

If you now enter the function = sheetname () in a cell, the sheet name is read out and output in the corresponding cell.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.