# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  how to get the name of the sheet in a cell?

## Alexandra

Hello,

I would like to know if there's a function or a macro to put the name of the current sheet in a cell like
="this sheet is called"&<function that returns the name of the sheet>

so that if a sheet's name is "kaput" it should appear "this sheet is called kaput"

Tks
Alexandra

----------


## dominicb

Good morning Alexandra

AFAIK there isn't a native Excel function that will do this, but you could write one very simply.  This would do the trick:

Function SheetName()
SheetName = ActiveSheet.Name
End Function

Then the formula =Sheetname() would return the sheetname, whilst this in a cell:

="This sheet is called " & Sheetname()

would return exactly what you requested.

HTH

DominicB

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Ashish Mathur

Hi,

In cell C4 in the sheet1, type the following formula

=cell("filename")

Now save and close the worksheet and reopen it.

In the cell in which you want the particular worksheet name type the
following array formula (Ctrl+Shift+Enter),

"This sheet is called " &MID(C4,MATCH("]",MID($C$4,ROW(1:200),1),0)+1,15)

Regards,

Ashish Mathur

"Alexandra" wrote:

>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile: http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>
>

----------


## JE McGimpsey

See

http://www.mcgimpsey.com/excel/formu..._function.html


..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
wrote:

> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra

----------


## Jack Sons

Alexandra,

Excel can do what you want. Put in any cell:

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

Jack Sons
The Netherlands

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> schreef
in bericht news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
> http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## Andy Wiggins

http://www.bygsoftware.com/Excel/functions/cell.htm

Workbook info using functions (and no VBA). Copy the formulas into your
workbook (If the workbook is new and has not been saved these formulas will
not work - there's no information for them to return!)

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("fi
lename",A1),1)-FIND("[",CELL("filename",A1),1)-1)

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


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

"Alexandra" <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com> wrote
in message news:Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com...
>
> Hello,
>
> I would like to know if there's a function or a macro to put the name
> of the current sheet in a cell like
> ="this sheet is called"&<function that returns the name of the sheet>
>
> so that if a sheet's name is "kaput" it should appear "this sheet is
> called kaput"
>
> Tks
> Alexandra
>
>
> --
> Alexandra
> ------------------------------------------------------------------------
> Alexandra's Profile:
http://www.excelforum.com/member.php...o&userid=26707
> View this thread: http://www.excelforum.com/showthread...hreadid=399701
>

----------


## JE McGimpsey

Note that you should use something like

=CELL("filename",A1)

instead. If you don't put in the cell reference, CELL() returns the name
of the last worksheet calculated, which may not be the sheet the cell
resides in.

There's no need to close the workbook and reopen it, though it does need
to be saved.

In article <90309944-A048-46F4-933D-86D51FE9BB30@microsoft.com>,
"Ashish Mathur" <mathurashish@hotmail.com> wrote:

> In cell C4 in the sheet1, type the following formula
>
> =cell("filename")
>
> Now save and close the worksheet and reopen it.

----------


## JE McGimpsey

Couple of quibbles:

1) You should use the reference argument to CELL() (e.g.,
CELL("fileneame", A1)) or it will return the name of the sheet in which
the last cell was changed/calculated. This may be a different sheet than
the one that the formula resides in.

2) Worksheet names can be 31 characters long. Using 30 in your MID
formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
indicate a big number).

In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
"Jack Sons" <j.sons@planet.nl> wrote:

> Excel can do what you want. Put in any cell:
>
> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## Jack Sons

Thanks JE, I didn't think of it.

Jack.

"JE McGimpsey" <jemcgimpsey@mvps.org> schreef in bericht
news:jemcgimpsey-269832.07524227082005@msnews.microsoft.com...
> Couple of quibbles:
>
> 1) You should use the reference argument to CELL() (e.g.,
> CELL("fileneame", A1)) or it will return the name of the sheet in which
> the last cell was changed/calculated. This may be a different sheet than
> the one that the formula resides in.
>
> 2) Worksheet names can be 31 characters long. Using 30 in your MID
> formula could truncate the worksheet name. I use 255 (2^8 - 1) just to
> indicate a big number).
>
> In article <uBsWDywqFHA.332@tk2msftngp13.phx.gbl>,
> "Jack Sons" <j.sons@planet.nl> wrote:
>
>> Excel can do what you want. Put in any cell:
>>
>> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)

----------


## gm2612

I cann't just express how lucky I feel. the following formulae worked perfetly for me. Thanks for posting it.

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

Thanks and regards,
gm2612





> See
> 
> http://www.mcgimpsey.com/excel/formu..._function.html
> 
> 
> ..In article <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>,
> Alexandra <Alexandra.1uf0yd_1125133530.5763@excelforum-nospam.com>
> wrote:
> 
> ...

----------


## NITIN.RAI

This will work for you. If you want the information in Cell A1 then replace A1 in the formula by any other cell.


=RIGHT(CELL("filename", $A$1),LEN(CELL("filename",$A$1))-FIND("]", CELL("filename",$A$1),1))

----------


## phoenixtiger

I use the following formula to get the sheet name:

=IF(ISERROR(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))),"",RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))

----------

