Forum Discussion

Tigger12b's avatar
Tigger12b
Copper Contributor
Feb 14, 2022
Solved

Formula to return the name of the worksheet

I have managed to find the formula to return the name of current filename and current worksheet in a cell in the worksheet =RIGHT(CELL("filename"),LEN(CELL("filename"))- MAX(IF(NOT(ISERR(SEA...
excel
Formulas and Functions
  • JMB17's avatar
    JMB17
    Feb 14, 2022

    Tigger12b 

     

    One caveat - if you don't include a cell reference for the CELL function, then the formula will return the name of the *active* worksheet when excel calculates or re-calculates the formula. So, if you enter the formula on Sheet2, it will return "Sheet2", but then if you go to Sheet1 and excel re-calculates, then your formula on Sheet2 will now say "Sheet1".

     

    I would use the same cell in which I entered the formula, so if you entered this in cell D2:
    =MID(CELL("filename",D2), FIND("]", CELL("filename",D2))+1,255)

     

    Personally, I like to set up a local named formula in name manager called TabName, using:
    =MID(CELL("filename",INDIRECT("A1")), FIND("]", CELL("filename",INDIRECT("A1")))+1,255)

     

    Of course, if you have the let function, you could eliminate the redundant part:

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

Lorenzo's avatar
Lorenzo
Silver Contributor
Feb 14, 2022

Hi Tigger12b 

Excel 2021/365

=LET(
    path, CELL("filename"),
    p,    FIND("]",path),
    MID(path,p+1,LEN(path)-p)
)

 

Other versions

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

Resources