Forum Discussion
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(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?
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))
2 Replies
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))
