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

TIA
--
Traa Dy Liooar

Jock

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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

Thanks Eduardo
--
Traa Dy Liooar

Jock


"Eduardo" wrote:

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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

Your welcome,

"Jock" wrote:

Thanks Eduardo
--
Traa Dy Liooar

Jock


"Eduardo" wrote:

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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

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" wrote in message
...
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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

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)

"Eduardo" wrote in message
...
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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

--

Dave Peterson

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" wrote in message
...
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)

"Eduardo" wrote in message
...
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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

--

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 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)

"Dave Peterson" wrote in message
...
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)

"Eduardo" wrote in message
...
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" wrote:

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

TIA
--
Traa Dy Liooar

Jock

--

Dave Peterson

--

Dave Peterson

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.

--
Rick (MVP - Excel)


"Jock" wrote in message
...
How can I display the ActiveSheet.name in cell C5 for instance?

TIA
--
Traa Dy Liooar

Jock

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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)

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" wrote in message
...
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

"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)