Formula referring to tab name

[jk_target]

Is there a formula that simply results in the name of the sheet that it is in?

 

[Dave Hawley]

You can nest the CELL function to obtain this, but my prefernce is this
much shorter custom function.

Function SheetName(Any_Cell As Range)
SheetName = Any_Cell.Parent.Name
End Function

Entered in any cell as:

=SheetName(A1)

To place in the code, push Alt+F11 and go to Insert>Module and paste in
the code.

*****Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum & Business Software*****

 

[Dave Peterson]

Debra Dalgleish has a list of Frequently Asked Questions at:

http://www.contextures.com/xlfaqIndex.html

In particular:
http://www.contextures.com/xlfaqFun.html#SheetName

 

[Norman Harker]

Hi jk_target!

Here's what to do and an explanation as to why plus a defined formula
approach:

We often need to find the name of the sheet. We can use it in formulas
or we might print it as a footer or header or use it in some index or
table of contents.
The formula used is:

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

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

To find 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 throw in 255 as the number of characters. I don't quite
know why we select 255 rather than 31 which is the maximum length of a
worksheet name, but I suppose it's because we always have done.

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

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



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.

Is there a formula that simply results in the name of the sheet that

it is in?

 

[jk_target]

Thanks, gents, for taking the time to help me!