+ Reply to Thread
Results 1 to 159 of 159

how to get the name of the sheet in a cell?

  1. #1
    Registered User
    Join Date
    08-27-2005
    Posts
    1

    how to get the name of the sheet in a cell?

    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

  2. #2
    Forum Expert dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    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

  3. #3
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  4. #4
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  5. #5
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  6. #6
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  7. #7
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  8. #8
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  9. #9
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  10. #10
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  11. #11
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  12. #12
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  13. #13
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  14. #14
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  15. #15
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  16. #16
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  17. #17
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  18. #18
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  19. #19
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  20. #20
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  21. #21
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  22. #22
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  23. #23
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  24. #24
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  25. #25
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  26. #26
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  27. #27
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  28. #28
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  29. #29
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  30. #30
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  31. #31
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  32. #32
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  33. #33
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  34. #34
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  35. #35
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  36. #36
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  37. #37
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  38. #38
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  39. #39
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  40. #40
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  41. #41
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  42. #42
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  43. #43
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  44. #44
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  45. #45
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  46. #46
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  47. #47
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  48. #48
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  49. #49
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  50. #50
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  51. #51
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  52. #52
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  53. #53
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  54. #54
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  55. #55
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  56. #56
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  57. #57
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  58. #58
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  59. #59
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  60. #60
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  61. #61
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  62. #62
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  63. #63
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  64. #64
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  65. #65
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  66. #66
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  67. #67
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  68. #68
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  69. #69
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  70. #70
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  71. #71
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  72. #72
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  73. #73
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  74. #74
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  75. #75
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  76. #76
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  77. #77
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  78. #78
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  79. #79
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  80. #80
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  81. #81
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  82. #82
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  83. #83
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  84. #84
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  85. #85
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  86. #86
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  87. #87
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  88. #88
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  89. #89
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  90. #90
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  91. #91
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  92. #92
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  93. #93
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  94. #94
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  95. #95
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  96. #96
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  97. #97
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  98. #98
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  99. #99
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  100. #100
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  101. #101
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  102. #102
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  103. #103
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  104. #104
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  105. #105
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  106. #106
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  107. #107
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  108. #108
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  109. #109
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  110. #110
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  111. #111
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  112. #112
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  113. #113
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  114. #114
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  115. #115
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  116. #116
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  117. #117
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  118. #118
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  119. #119
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  120. #120
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  121. #121
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  122. #122
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  123. #123
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  124. #124
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  125. #125
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  126. #126
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  127. #127
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  128. #128
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  129. #129
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  130. #130
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  131. #131
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  132. #132
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  133. #133
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  134. #134
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  135. #135
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  136. #136
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  137. #137
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  138. #138
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  139. #139
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  140. #140
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  141. #141
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  142. #142
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  143. #143
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  144. #144
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  145. #145
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  146. #146
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  147. #147
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  148. #148
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  149. #149
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  150. #150
    Ashish Mathur
    Guest

    RE: how to get the name of the sheet in a cell?

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


  151. #151
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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


  152. #152
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Alexandra,

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

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

    Jack Sons
    The Netherlands

    "Alexandra" <[email protected]> schreef
    in bericht news:[email protected]...
    >
    > 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
    >




  153. #153
    Andy Wiggins
    Guest

    Re: how to get the name of the sheet in a cell?

    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" <[email protected]> wrote
    in message news:[email protected]...
    >
    > 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
    >




  154. #154
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Ashish Mathur" <[email protected]> wrote:

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


  155. #155
    JE McGimpsey
    Guest

    Re: how to get the name of the sheet in a cell?

    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 <[email protected]>,
    "Jack Sons" <[email protected]> wrote:

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


  156. #156
    Jack Sons
    Guest

    Re: how to get the name of the sheet in a cell?

    Thanks JE, I didn't think of it.

    Jack.

    "JE McGimpsey" <[email protected]> schreef in bericht
    news:[email protected]...
    > 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 <[email protected]>,
    > "Jack Sons" <[email protected]> wrote:
    >
    >> Excel can do what you want. Put in any cell:
    >>
    >> =MID(CELL("filename"),FIND("]",CELL("filename"))+1,30)




  157. #157
    Registered User
    Join Date
    07-29-2013
    Location
    India
    MS-Off Ver
    Excel 2002
    Posts
    53

    Re: how to get the name of the sheet in a cell?

    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

    Quote Originally Posted by JE McGimpsey View Post
    See

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


    ..In article <[email protected]>,
    Alexandra <[email protected]>
    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

  158. #158
    Registered User
    Join Date
    02-11-2014
    Location
    BOMBAY
    MS-Off Ver
    EXCEL 2010
    Posts
    1

    Re: how to get the name of the sheet in a cell?

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

  159. #159
    Registered User
    Join Date
    05-22-2013
    Location
    Bolton, England
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: how to get the name of the sheet in a cell?

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1