Creating the name of the Activesheet using a worksheet formu

  • Is there a formula that I can use in spreadsheet to show the sheet name.


    I know that Excel has the cell function to return the filename and path (=CELL(filename))


    I forgot the last argument in that function.


    Is there a way of displaying the name of a worksheet without using VBA?


    Thanks

  • Hi,


    Quote


    Is there a way of displaying the name of a worksheet without using VBA?


    Yes, try following:


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


    At this moment I´m using a swedish version so I might get the "Filename" wrong.

  • Thank you, it works well.


    If I wanted to only display the first three letters of the worksheet, how would I modify the formula?


    If I had April summary, for example, as the worksheet name but I wanted to show in a box on a worksheet only "Apr", --I don't want to use a date function by the way -- how would I modify the code?


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

  • I could use the Left function to display it.


    However, is there a way of modifying the original code without using the Left function?

  • just change it to the following for the first 3 letters:


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


    the last argument is the number of letters to include...

  • =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,3)



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


    Interesting. Each formula provides a different function. What is it though?

  • The difference in the two formulas is the portion that begins with the Len function:


    LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))


    Is the Len piece redundant code? Also, why is the find function included to subtract the "]" from the file name?


    Thanks

  • The LEN part determines the number of characters in the filename. The FIND part finds the position of the ]. The subtraction gets the length of the sheet name. If you only want the first 3 characters then the above all gets replaced with the 3, otherwise the calculation is needed.

  • In other words, the entire sheet name is subtracted from the entire path.


    Whereas, the other provides a portion of the sheet name?

  • yes.... up until that last argument you have just located the starting position. So in your example, it has found where the A in April is located... but now you need to tell it how many letters to get.


    The last argument tells it how many characters you want to retrieve from that starting point. If you know that you absolutely only want the first 3 letters then you can put a 3 in for it.


    In most cases, people are looking for the entire sheet name. For the last argument, Dennis took the length of the entire filename minus the len of the string up to the position for our first letter to find the number of characters for the sheet name.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!