Insert Tab name in sheet?

[Muller]

As part of my spredsheet i want to concatenate the Tab
value in the formula.

 

[RedViking]

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:

 

[Jacques Brun]

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

Copy the code in the module
Close the VB Windows to return to Excel

 

[Max]

.. i want to concatenate the Tab value in the formula.

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

 

[Muller]

Many thanks to all the help