Sheet names
- Jul. 7, 2019
 | Example files with this article: |
Introduction
Sheet names are bound to change from time to time. Whenever you wish to use the name of a sheet in a cell, then traditionally we have a rather long formula to do this. The aim of this article is not only to give you the formula, but in the first place to simplify (shorten) the formula and make it more robust !
The formula that you find regularly, is: =MID(CELL("filename",A1)),FIND("]",CELL("filename",A1)))+1,255)
Problems ask for solutions
If you paste this beast into a cell, this will work. If you paste it into a sheet called "Bart", then you will see "Bart" in the cell. This can be useful for example when you are writing documentation in Excel, and afterwards sheet names are changing: you can avoid additional effort. Please follow along based on the file that you can download at the top of the page.
But hold on, there is a problem. If you copy the cell to a different sheet, then A1 will still refer to A1 on the first sheet. You will get a #REF! reference error. OK, then let's try to make the cell reference absolute with dollar signs: $A$1. This is better ! You can copy/paste over sheets.
Well, copy/paste is fun and easy, but I find that the formula is too long-winded. I would prefer to just see it like this: =sheetname, whereby sheetname is a name in the workbook we are working in.That name will contain the formula. It is sufficient to put this in the correct of every such sheet: =sheetname. Wouldn't that be nice ?
Let's create a name. The easiest way is to press Ctrl + F3, you will end up in the correct dialog screen. Alternatively, you go to Formulas > Names. Add a new name (with workbook scope instead of worksheet scope): sheetname_long: =MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255)
Maybe you spotted that in the formula above I replaced A1 with the INDIRECT function. We do this because A1 will not refer back to A1 at other sheets. INDIRECT is useful but it has a bad reputation because it can slow down (big) Excel workbooks. If you use it like this, it's very fine though !
Whenever we type =sheetname_long in any cell, we get the name of the sheet. What you can observe in the formula is that we use twice the part on CELL(...). Why not creating a second name with only that piece of the formula ? Next, we will use the new name twice in the formula for the sheet name. Like that, you will have a shorter solution (with 2 names instead of 1).
This is how we end up with the formula of the attached file: =sheetname, which in turn uses _fn (you can call it whatever you like). Please allow me to add 2 important remarks.
- in a non-English Excel-environment you can replace the part of filename with a localized variant. However, that localized variant will not work elsewhere, whereas the variant in English with filename is guaranteed to work everywhere
- The A1 argument in the INDIRECT function is a piece of text (string). It is generally safer to use numbers rather than hardcoded text strings. The ADDRESS function allows us to do that. You can find it in the example file above: ADDRESS(1,1,1)
Please visit and read this page by Ron De Bruin about these international issues.
An alternative formula using the LET function: =LET( f, CELL("filename",INDIRECT("A1")), MID(f,FIND("]",f)+1,255))
That's all for today. Enjoy Sunday.
