SOLVED

Formula to return the name of the worksheet

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3164144%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EFormula%20to%20return%20the%20name%20of%20the%20worksheet%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3164144%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20have%20managed%20to%20find%20the%20formula%20to%20return%20the%20name%20of%20current%20filename%20and%20current%20worksheet%20in%20a%20cell%20in%20the%20worksheet%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CDIV%20class%3D%22%5C%26quot%3B%5C%26quot%3B%22%3E%3CP%3E%3CSTRONG%3E%3DRIGHT(CELL(%22filename%22)%2CLEN(CELL(%22filename%22))-%20MAX(IF(NOT(ISERR(SEARCH(%22%5C%5C%22%2CCELL(%22filename%22)%2C%20ROW(1%3A255))))%2CSEARCH(%22%5C%5C%22%2CCELL(%22filename%22)%2CROW(1%3A255)))))%26lt%3B%5C%2FSTRONG%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EHowever%20I%20want%20to%20get%20just%20the%20name%20of%20the%20worksheet%2C%20but%20am%20having%20some%20difficulty%20determining%20what%20the%20formula%20should%20be%20to%20get%20that%2C%20can%20anyone%20help%3F%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2FDIV%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3164144%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

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(SEARCH("\",CELL("filename"), ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))

However I want to get just the name of the worksheet, but am having some difficulty determining what the formula should be to get that, can anyone help?

2 Replies

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"))
)
best response confirmed by Tigger12b (Occasional Visitor)
Solution

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