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

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" <(E-Mail Removed)> wrote in message
news:326AEE94-4D65-447F-A069-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
news:2CF06D7A-2B2A-4A44-8006-(E-Mail Removed)...
> 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

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" <(E-Mail Removed)> wrote in message
> news:326AEE94-4D65-447F-A069-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:326AEE94-4D65-447F-A069-(E-Mail Removed)...
>> > 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

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" <(E-Mail Removed)> wrote in message
news:EF072FFA-C110-446E-9672-(E-Mail Removed)...
> 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)
>>
>>
>