1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL.MI

  • A fun puzzle...

    From dpb@21:1/5 to All on Fri May 28 14:21:14 2021
    I was trying to build a formula to return the sheet name in a cell --

    =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

    works for a given sheet, but it breaks when trying to use on more than
    one sheet in a workbook as it is always trying to reference the first sheet.

    Is there any way to get a numeric reference to the ordinal sheet
    position in the CELL() reference position (other than using the text
    sheet name expressly which defeats the whole ease of use thing for
    wanting such)?

    --

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Fri May 28 22:44:48 2021
    Hi Duane,

    Am Fri, 28 May 2021 14:21:14 -0500 schrieb dpb:

    I was trying to build a formula to return the sheet name in a cell --

    =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

    works for a given sheet, but it breaks when trying to use on more than
    one sheet in a workbook as it is always trying to reference the first sheet.

    Is there any way to get a numeric reference to the ordinal sheet
    position in the CELL() reference position (other than using the text
    sheet name expressly which defeats the whole ease of use thing for
    wanting such)?

    CELL needs a second argument, a reference.
    Try:
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)


    Regards
    Claus B.
    --
    Windows10
    Microsoft 365 for business

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From dpb@21:1/5 to Claus Busch on Fri May 28 17:15:48 2021
    On 5/28/2021 3:44 PM, Claus Busch wrote:
    Hi Duane,

    Am Fri, 28 May 2021 14:21:14 -0500 schrieb dpb:

    I was trying to build a formula to return the sheet name in a cell --

    =REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")

    works for a given sheet, but it breaks when trying to use on more than
    one sheet in a workbook as it is always trying to reference the first sheet. >>
    Is there any way to get a numeric reference to the ordinal sheet
    position in the CELL() reference position (other than using the text
    sheet name expressly which defeats the whole ease of use thing for
    wanting such)?

    CELL needs a second argument, a reference.
    Try:
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)


    Regards
    Claus B.


    Aha! I interpreted the optional reference help description incorrectly
    as being the same thing...then the examples I found used a sheet text
    name, not a cell.

    Thanks, that does fix the problem. Why there isn't a direct function is mindboggling.

    --

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)