Referencing Worksheet Tabs

My Excel Toolbox includes the following function to return information about Target (a cell or range); default Target is the formula's cell:
    =NameOf([This],[Target])
The first parameter This can be "sheet" (or "worksheet"), "book" (or "workbook"), "path" (or "filepath"), "app" (or "application"), "caption" (or "titlebar"), "statusbar", "user", "organization", "printer", "computer", "?" (or "help"), or the name of an environment variable (like "TEMP"). Default value is "sheet" (or "worksheet"); therefore, the following formula will return the name of the worksheet's tab:
    =NameOf()
This function is similar to Excel's CELL and INFO functions, but perhaps more useful. In particular, CELL("filename") is blank ("") if the workbook is new and unsaved. (See the Tip's caveat.) And CELL("filename") returns the "wrong" result if another open workbook is active. NameOf() does not have these issues. See https://sites.google.com/view/MyExcelToolbox/
For related discussion, see https://excelribbon.tips.net/T013432_Inserting_the_Workbook_Name.html

This can now be more simply done by
=TEXTAFTER(CELL("filename"),"]")

Modify Alan's formula slightly to get the whole tab name, no matter how long it is:

=MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

The mod subtracts the position of the square bracket from the length of the filename string, giving the length of the worksheet tab name.

I use this formula to put the worksheet name in A1.

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

There is an easier way to use the tab name, by using the "indirect" function.
E.g. On Sheet1, cells A1-A3, type in a1, a2, a3. In cells B1-B3 type in the name of the sheet you want to use, whether it is a date (ensure the cell is formatted as text) or other text. In cells c1-c3 use the formula: =indirect(B1&"!"&A1,True). The result will be the value in the cell on the target sheet.

Regards, Rene

One can use this function/macro to get the sheet name into a cell:

Function shName(rng As Range) As String
Application.Volatile
shName = rng.Worksheet.Name
End Function

... and then put this formula into the desired cell:

=shname(A1)

@Mark
Further to MA's comment, if you omit the ',10' you'll get a 'too few arguments' error.

The value of 10 is not a mistake given the caveats in the article ("If the names of your worksheet tabs consist only of dates (no other text in them)" and "Dates can be expressed in a maximum of 10 characters, as in 12-31-2011."). But I think "extracts the first eight characters" should read "extracts the first ten characters". And if someone uses a format like dd-mmm-yyyy they'll need 11 characters. Using ',31' does make the function generic. The inclusion of $A$1 reference in the CELL function is critical, otherwise the formula will return whichever tab name was most recently changed.

@Mark,
Did you managed to use the MID function leaving out the "Num_chars" argument - which, to the best of my knowledge is
not(!) optional.
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL

Just leave out the ",10" and then it will work for any length name with no margin for error.

@@@ To whom it may concern and with all doe respect - I have 2 meaning full comments:
1) The value of 10 (in the MID function) is a mistake, especially if the sheets name exceeds 10 characters(*** by fact it can be up to 31 characters).
2) The lack of the reference to $A$1 will make Excel to misinterpret the formula when copied to other sheets.
Try to enter this tips formula in sheet1 (in cell D5) > copy it and paste in it Sheet2 cell D5.
Check, now, the results, of the formulas, in the two sheets.
So, my suggested formula will look like that:

=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)

Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2015)
ISRAEL