Insert Tab name in sheet?

  • Thread starter Thread starter Muller
  • Start date Start date
You can use the function

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;255)

to get the tab-name into a cell.

Regards,
RedViking:
 
One solution is to add the following function in a Visual
Basic module:

Option Explicit

Function Sheetname() As String
Sheetname = ActiveSheet.Name
End Function


Then including "Sheetname()" in a formula will return the
name of the Tab

if the active Tab is "Sheet1"
if A1 contains ="The name of the sheet is "&Sheetname()
A1 will display as: "The name of the sheet is Sheet1"

Caution: If the Tab is changed the cells containing the
formula are not automatically updated !!!!! (because Excel
ignores that the function Sheetname is depending on the
name of the Tab...)

To create a visual basic module:
Tools >Macros >Visual Basic Editor
insert >Module
Click to expand...
Copy the code in the module
Close the VB Windows to return to Excel
 
Muller said:
.. i want to concatenate the Tab value in the formula.
Click to expand...

Perhaps what you're after is INDIRECT(),
instead of "concatenate" ..

Example: Suppose you have

In Sheet1
-------------
In A1: Sheet2
In B1: A1

If you put in C1: =INDIRECT(A1&"!"&B1)

Above is functionally equivalent to : =Sheet2!A1

C1 will return the value in cell A1 in Sheet2
 
Back
Top