Tabname in cell

[Ben Nederhand]

I am looking for a function to get the tabname in a cell. If the name in the
tab is changed then automatically the name in the cell is adapted. Any
suggestion is much appreciated

 

[Norman Harker]

Hi Ben!

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



But some explanation might help you understand the use of three
functions and some of the logic that isn't that obvious if you don't
have it pointed out to you.



=CELL("filename")

Returns the full drive, folder, file name and sheetname of a workbook
provided that it has been saved.



However, the CELL function is volatile which means that it
recalculates every time the workbook gets recalculated. This means
that in the form given it will return the worksheet name of whatever
the active sheet is at the time of recalculation. So if you put this
in Sheet1 and then go to Sheet2 and do some calculation, when you come
back to Sheet1 the reference will be to Sheet2. So we modify the
formula by putting a reference to any cell in the sheet we want the
name for.



=CELL("filename",A1)



A sample return might be:

C:\My Files\NewsGroups\Posting Testers\2003-02\[2003-02-08 Sheet
Name.xls]Sheet1



All we want is the worksheet name at the end.



MID is a text 'parsing' function that has the syntax:

=MID(text,start_num,num_chars)

In this case:

CELL("filename",A1) provides us with the text that we are 'parsing'.



We use:

FIND("]",CELL("filename",A1))+1

Finds the position of the first cell after the ] which is always where
the worksheet name's first character will be located. FIND has syntax:

=FIND(find_text,within_text,start_num)

Find_text is "]", CELL("filename", A1) gives us the within_text and
start_num is optional and not needed if we want to search the entire
'string' of text. Obvious the position of the first character of the
worksheet name is 1 more than the position number of the ] that
encloses the workbook's name.



Finally we often throw in 255 as the number of characters. I prefer 32
because 31 is the maximum length of a worksheet name.



But a good defined formula approach may be to define a formula as:



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



If you define sh.name as that formula you can then use =sh.name to get
the sheet that the cell is in.


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Nick Hodge]

Ben

This should do it in it's simplest form. Obviously the cell value will
change when you move sheets, but you won't be able to see it! You will
sense it though if many calculations go on based on it

Function InsertTabName() As String
Application.Volatile True
InsertTabName = ActiveSheet.Name
End Function

You copy this function into a standard module. Either in the workbook or
your personal.xls. (Alt+F11 and then insert>module. Paste the code here)

You can use this like any other function

=InsertTabName()

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Andy]

A bit more than you asked for:

The file path and name
=CELL("filename",A1)

The file path
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename"
,A1),1))


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"