How To Name Worksheet In Excel From A Cell

Often, y'all need to insert and piece of work with the canvas name in an Excel sail, for instance if you are working with the 'INDIRECT'-formula. Or, if you want to dynamically alter headlines depending on the sheet proper name. If you don't desire to type the canvass name manually – which is very unstable – in that location are three ways to get a sheet proper noun

Earlier we first: If y'all just take to insert the canvas name for a pocket-size corporeality of worksheets, please consider doing information technology manually. It commonly is the fastest way.

Contents

Method 1: Insert the canvass name using built-in Excel functions
Method 2: Render the sheet name using VBA
Method 3: Utilize 'Professor Excel Tools'
Example: Have it a step further: Insert and clean a worksheet proper name
Download example canvass and further reading
- Also interesting:

Method 1: Insert the sheet name using built-in Excel functions

The easiest way is to copy the following role and paste it into your Excel cell:

                      =RIGHT(CELL("filename",A1),LEN(Cell("filename",A1))-SEARCH("]",CELL("filename",A1)))

This formula obtains the filename "=CELL("filename",A1)" and separates it later the graphic symbol "]". If you desire to get the name of some other Excel canvas, y'all have to alter the jail cell reference from "A1" to whatsoever jail cell of the other worksheet. And depending on your version and language of Excel, you might have to interpret the function names and maybe replace "," past ";".

Insert a sheet name with the cell function.

The big advantages of this method is that it doesn't crave any programming in VBA or a third-party Excel add together-in.

On the downside, delight note the following comments:

If you open up a new file and paste this part, it won't piece of work before saving it.
The prison cell office is volatile. That means, information technology always calculates no matter if yous've changed annihilation. This is a disadvantage for large Excel files where the performance of calculation is crucial.
Likewise, the cell function doesn't translate to other languages. If your Excel is fix to High german, Spanish etc., you lot have to supersede the "filename" part with the corresponding word in your language.

Do you want to heave your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!

Method ii: Return the sail name using VBA

The second mode to go a worksheet proper name uses VBA. In one case set-up, information technology might be easier to use. You take to open the developer tools and add a module. Next, you can use this code:

          Function PROFEXSheetName(cell As Range) Equally String     'This function returns the name of a selected worksheet     '"prison cell" contains the link to a prison cell on the worksheet          Awarding.Volatile     'With "Application.Volatile" yous can make sure, that the function will be recalculated one time the worksheet is recalculated          PROFEXSheetName = cell.Worksheet.Name     'Returns the worksheet proper noun      Terminate Function

Now, you can use the function "=PROFEXSheetName(A1)" in your Excel prison cell and the worksheet name volition be shown.

VBA macro for inserting a sheet name into an Excel cell. — VBA macro for inserting a sheet proper noun into an Excel jail cell.

As the result of both ways is (or should be…) the aforementioned, it is upwardly to you which fashion to cull. If you feel comfortable using an easy macro, you lot probably go for the 2d way. If not, just paste the formula from the first way to your prison cell.

Method 3: Utilize 'Professor Excel Tools'

Because returning the sheet name is a mutual problem in Excel, it's included in the Excel add-in 'Professor Excel Tools'. Just type =PROFEXSheetName(A1) and it'll render the canvas name. Download the gratis trial below.

Please note: for using this feature of Professor Excel Tools, yous don't accept to buy a license. The built-in Excel functions are gratis to utilise in order to guarantee compatibility.

This part is included in our Excel Add-In 'Professor Excel Tools'

(No sign-up, download starts straight)

Example: Take information technology a pace farther: Insert and clean a worksheet proper name

I usually recommend not using space characters in worksheet names. It has some disadvantages, especially when linking to other worksheets (read more than nigh worksheet names hither).

So, I usually use underscore "_"-characters to split words in sail names. Allow's assume the following example: The worksheet name should at the same time exist displayed in a heading. Here is how to do it, using the method one from higher up.

The approach: Wrap the substitute part around the sheet name part:

Cleaning and inserting the worksheet name from underscores. — Cleaning and inserting the worksheet proper noun from underscores.

So, the role is:

                      =SUBSTITUTE(RIGHT(Jail cell("filename",A1),LEN(CELL("filename",A1))-SEARCH("]",CELL("filename",A1))),"_"," ")

Download instance sheet and further reading

Please download the example file here. It contains examples for the methods two and three from above.

You lot don't want to insert the worksheet name just rather the whole path, file name etc.? Check this article. Information technology has everything, you need!