Display tab name in cell

E

Eduardo

Hi,
copy the formula as follow in the cell where you want to display the name

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

If this was helpful please say yes. Thank you
 
J

Jock

Thanks Eduardo
--
Traa Dy Liooar

Jock


Eduardo said:
Hi,
copy the formula as follow in the cell where you want to display the name

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

If this was helpful please say yes. Thank you

Jock said:
How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
 
R

Rick Rothstein

I think you can safely leave the optional cell reference argument off of the
function calls as the file name would be the same no matter what cell is
referenced...

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

--
Rick (MVP - Excel)


Eduardo said:
Hi,
copy the formula as follow in the cell where you want to display the name

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

If this was helpful please say yes. Thank you

Jock said:
How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
 
R

Rick Rothstein

Eduardo has given you a worksheet formula solution to your question;
however, since you posted your question in the "programming" newsgroup, I
thought you might be looking for a UDF (User Defined Function). The UDF for
this question is quite simple...

Function TabName()
TabName = ActiveSheet.Name
End Function

To install the UDF, press Alt+F11 to go into the VB editor and click
Insert/Module from its menu bar, then copy/paste the above code into the
code window that opened up there. To use the UDF, just place this formula...

=TabName()

into any cell that you want the TabName to appear in.
 
E

Eduardo

Your welcome,

Jock said:
Thanks Eduardo
--
Traa Dy Liooar

Jock


Eduardo said:
Hi,
copy the formula as follow in the cell where you want to display the name

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

If this was helpful please say yes. Thank you

Jock said:
How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick said:
I think you can safely leave the optional cell reference argument off of the
function calls as the file name would be the same no matter what cell is
referenced...

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

--
Rick (MVP - Excel)

Eduardo said:
Hi,
copy the formula as follow in the cell where you want to display the name

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

If this was helpful please say yes. Thank you

Jock said:
How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Nothing odd happens when I do that... the tab name appears the same in all
the windows for that formula. What are you suggesting should have happened?
My XL2003 is at Service Pack 2 if that might matter.

--
Rick (MVP - Excel)


Dave Peterson said:
This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick said:
I think you can safely leave the optional cell reference argument off of
the
function calls as the file name would be the same no matter what cell is
referenced...

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

--
Rick (MVP - Excel)

Eduardo said:
Hi,
copy the formula as follow in the cell where you want to display the
name

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

If this was helpful please say yes. Thank you

:

How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
E

egun

Rick,

I also thought he might like the UDF solution, but didn't offer it because
he seemed to like the formula route well enough. However, when I created the
UDF, it didn't always update when I manually CHANGED the sheet name. Then I
added Application.Volatile vbTrue, and that seemed to make it work. Is that
your experience also?

Thanks,

Eric
 
R

Rick Rothstein

Good catch! I completely forgot about the possibility the sheet name might
get changed. As you indicated, this is how the code should have be
constructed...

Function TabName()
Application.Volatile vbTrue
TabName = ActiveSheet.Name
End Function

Thanks for catching that omission for me.

--
Rick (MVP - Excel)


egun said:
Rick,

I also thought he might like the UDF solution, but didn't offer it because
he seemed to like the formula route well enough. However, when I created
the
UDF, it didn't always update when I manually CHANGED the sheet name. Then
I
added Application.Volatile vbTrue, and that seemed to make it work. Is
that
your experience also?

Thanks,

Eric
Click to expand...
 
D

Dave Peterson

Without the reference to the cell, the formula will use the activesheet (in the
activeworkbook).

Are you sure that you had multiple different windows in view?

Rick said:
Nothing odd happens when I do that... the tab name appears the same in all
the windows for that formula. What are you suggesting should have happened?
My XL2003 is at Service Pack 2 if that might matter.

--
Rick (MVP - Excel)

Dave Peterson said:
This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick said:
I think you can safely leave the optional cell reference argument off of
the
function calls as the file name would be the same no matter what cell is
referenced...

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

--
Rick (MVP - Excel)

Hi,
copy the formula as follow in the cell where you want to display the
name

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

If this was helpful please say yes. Thank you

:

How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
R

Rick Rothstein

Okay, I see what you mean. I really do hate the CELL function as it seems to
be an odd one as functions go.

--
Rick (MVP - Excel)


Dave Peterson said:
Without the reference to the cell, the formula will use the activesheet
(in the
activeworkbook).

Are you sure that you had multiple different windows in view?

Rick said:
Nothing odd happens when I do that... the tab name appears the same in
all
the windows for that formula. What are you suggesting should have
happened?
My XL2003 is at Service Pack 2 if that might matter.

--
Rick (MVP - Excel)

Dave Peterson said:
This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least
once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick Rothstein wrote:

I think you can safely leave the optional cell reference argument off
of
the
function calls as the file name would be the same no matter what cell
is
referenced...

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

--
Rick (MVP - Excel)

Hi,
copy the formula as follow in the cell where you want to display the
name

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

If this was helpful please say yes. Thank you

:

How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

There are legal (from Windows standpoint) that make the =cell() formulas even
uglier.

Rick said:
Okay, I see what you mean. I really do hate the CELL function as it seems to
be an odd one as functions go.

--
Rick (MVP - Excel)

Dave Peterson said:
Without the reference to the cell, the formula will use the activesheet
(in the
activeworkbook).

Are you sure that you had multiple different windows in view?

Rick said:
Nothing odd happens when I do that... the tab name appears the same in
all
the windows for that formula. What are you suggesting should have
happened?
My XL2003 is at Service Pack 2 if that might matter.

--
Rick (MVP - Excel)

This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least
once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick Rothstein wrote:

I think you can safely leave the optional cell reference argument off
of
the
function calls as the file name would be the same no matter what cell
is
referenced...

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

--
Rick (MVP - Excel)

Hi,
copy the formula as follow in the cell where you want to display the
name

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

If this was helpful please say yes. Thank you

:

How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

There are legal (from Windows standpoint) *file names* that make the =cell()
formulas even
uglier.

(I left out a couple of words.)

Dave said:
There are legal (from Windows standpoint) that make the =cell() formulas even
uglier.

Rick said:
Okay, I see what you mean. I really do hate the CELL function as it seems to
be an odd one as functions go.

--
Rick (MVP - Excel)

Dave Peterson said:
Without the reference to the cell, the formula will use the activesheet
(in the
activeworkbook).

Are you sure that you had multiple different windows in view?

Rick Rothstein wrote:

Nothing odd happens when I do that... the tab name appears the same in
all
the windows for that formula. What are you suggesting should have
happened?
My XL2003 is at Service Pack 2 if that might matter.

--
Rick (MVP - Excel)

This isn't true.

To test:

Create a workbook with a couple of worksheets (and save it at least
once)
then use this formula (without the range reference).
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

And show multiple windows (window|new window in xl2003 menus)

And recalculate (hit F9) and watch what each formula evaluates to.


Rick Rothstein wrote:

I think you can safely leave the optional cell reference argument off
of
the
function calls as the file name would be the same no matter what cell
is
referenced...

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

--
Rick (MVP - Excel)

Hi,
copy the formula as follow in the cell where you want to display the
name

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

If this was helpful please say yes. Thank you

:

How can I display the ActiveSheet.name in cell C5 for instance?

TIA
Click to expand...
Click to expand...
Click to expand...
 
Top