Display tab name in cell

  • Thread starter Thread starter Jock
  • Start date Start date
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
 
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...
 
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...
 
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.
 
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...
 
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...
 
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...
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top