I may be overlooking something simple but how can I echo the worksheet name
in a particular cell. FOr example, I want cell A1 to always contain the
name of the worheet that cell is a part of. Cell A1 of the worksheet
"SHEET1" would contain "SHEET1". I would need it to update if the sheet
name were changed as well.

Hi Shawn

not all that simple
use this formula to put the sheet name in a cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

BTW you need to save the workbook first.
--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Shawn Shuler" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I may be overlooking something simple but how can I echo the worksheet name
> in a particular cell. FOr example, I want cell A1 to always contain the
> name of the worheet that cell is a part of. Cell A1 of the worksheet
> "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> name were changed as well.
>
>

The file path and name
CELL("filename",A1)

The file path
LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

The file name
MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fil
ename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

The sheet name
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
A1),1))

--
Regards
-
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy


"Shawn Shuler" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I may be overlooking something simple but how can I echo the worksheet
name
> in a particular cell. FOr example, I want cell A1 to always contain the
> name of the worheet that cell is a part of. Cell A1 of the worksheet
> "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> name were changed as well.
>
>

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shawn Shuler" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I may be overlooking something simple but how can I echo the worksheet
name
> in a particular cell. FOr example, I want cell A1 to always contain the
> name of the worheet that cell is a part of. Cell A1 of the worksheet
> "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> name were changed as well.
>
>

See http://www.xldynamic.com/source/xld.xlFAQ0002.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shawn Shuler" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I may be overlooking something simple but how can I echo the worksheet
name
> in a particular cell. FOr example, I want cell A1 to always contain the
> name of the worheet that cell is a part of. Cell A1 of the worksheet
> "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> name were changed as well.
>
>

Since a sheet name can only have 31 characters you can shorten that to

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

Regards,

Peo Sjoblom

"JulieD" wrote:

> Hi Shawn
>
> not all that simple
> use this formula to put the sheet name in a cell
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
>
> BTW you need to save the workbook first.
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "Shawn Shuler" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> >I may be overlooking something simple but how can I echo the worksheet name
> > in a particular cell. FOr example, I want cell A1 to always contain the
> > name of the worheet that cell is a part of. Cell A1 of the worksheet
> > "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> > name were changed as well.
> >
> >
>
>
>

Hi Peo

didn't know this, thanks

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Peo Sjoblom" <(E-Mail Removed)> wrote in message
news:CC014BD9-4BFC-41A3-8F2A-(E-Mail Removed)...
> Since a sheet name can only have 31 characters you can shorten that to
>
> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,31)
>
> Regards,
>
> Peo Sjoblom
>
> "JulieD" wrote:
>
>> Hi Shawn
>>
>> not all that simple
>> use this formula to put the sheet name in a cell
>> =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
>>
>> BTW you need to save the workbook first.
>> --
>> Cheers
>> JulieD
>> check out www.hcts.net.au/tipsandtricks.htm
>> ....well i'm working on it anyway
>> "Shawn Shuler" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> >I may be overlooking something simple but how can I echo the worksheet
>> >name
>> > in a particular cell. FOr example, I want cell A1 to always contain
>> > the
>> > name of the worheet that cell is a part of. Cell A1 of the worksheet
>> > "SHEET1" would contain "SHEET1". I would need it to update if the
>> > sheet
>> > name were changed as well.
>> >
>> >
>>
>>
>>

What's wrong about this particular solution? Works nicely for me.

Private Sub Workbook_Open()
ThisWorkbook.Worksheets(1).Range("A1") = ThisWorkbook.Worksheets(1).Name

End Sub


Gord.

The OP wrote:

> Cell A1 of the worksheet "SHEET1" would contain "SHEET1". I would
> need it to update if the sheet name were changed as well.

Your solution won't update until the workbook is closed and reopened. It
also depends on macros being enabled.

In practice your solution may be adequate for the OP, but it doesn't
meet the specification.


In article <N6udnSdX6dJ4tOTfRVn-(E-Mail Removed)>,
"gord" <gord@no_spaming.com> wrote:

> What's wrong about this particular solution? Works nicely for me.
>
> Private Sub Workbook_Open()
> ThisWorkbook.Worksheets(1).Range("A1") = ThisWorkbook.Worksheets(1).Name
>
> End Sub

I find that this one:

RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
A1),1))

is the only one that updates automatically as the sheet name is changed.
EXACTLY what I was looking for.


"Andy Wiggins" <(E-Mail Removed) o m> wrote in message
news:(E-Mail Removed)...
> The file path and name
> CELL("filename",A1)
>
> The file path
> LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
>
> The file name
>
MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("fil
> ename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
>
> The sheet name
>
RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",
> A1),1))
>
> --
> Regards
> -
> Andy Wiggins FCCA
> www.BygSoftware.com
> Excel, Access and VBA Consultancy
>
>
> "Shawn Shuler" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > I may be overlooking something simple but how can I echo the worksheet
> name
> > in a particular cell. FOr example, I want cell A1 to always contain the
> > name of the worheet that cell is a part of. Cell A1 of the worksheet
> > "SHEET1" would contain "SHEET1". I would need it to update if the sheet
> > name were changed as well.
> >
> >
>
>