I tried to find simple function to return the worksheet name from a Code Name. As I was searching, many said I could not do it.  If you know me, I don’t like it when someone says I can’t do something especially in Excel.

Many searches consistently led me in the wrong direction.  Thankfully through my determination, I was able to find this post that was close to what I needed and is the bases for my function, but I had to make modifications to meet my needs.  Their function results in the worksheet object and requires the user to pass the workbook object, whereas my function assumes ActiveWorkbook and results in the string name of the worksheet.

If you are not aware, each worksheet has a Code Name that starts with “Sheet” and is followed by a number. This is what the worksheet name was before it was assigned a unique name that is seen on the tab of the worksheet. You can see the Code Names and their assigned unique name within the Visual Basic Editor under Microsoft Excel Objects, which is typically in the top left corner (see below).

I am always looking for ways to fool proof my projects because there are other users of my files. Unfortunately I have to think of ways other users may break my files. They are not tech savvy, but most of the time they break my files due to careless mistakes.

A common concern I have is that a user may change the name of a worksheet and not realize how that impacts other formulas. I have used the INDIRECT function to create dynamic cell ranges. If you are familiar with using INDIRECT, you have to either fix the worksheet name or part of the cell range or come up with formulas to derive those names or values. As for the worksheet name, you could simply use something like the following (code source):

Call me paranoid or a prepper, but one flaw of that formula is the “A1” reference. If someone just so happens to delete all of row 1 or something similar, then “A1” will turn into a “REF!” error, which is No Bueno.

That simple act could blow up what seemed like a perfectly designed automated process and then you get a barrage of calls, texts and emails from those other users expecting you to fix it on the spot. A simple problem like this may be easy to spot, but why not fool proof it even more. We want to be the Invincible Iron Man, right!

Although we are looking for invincible, all hero’s have weaknesses. So, we are assuming that users are not so careless to delete a worksheet and are not sophisticated enough to change the Code Name of a worksheet. They also are not going to intentionally change a key value in a formula that I will share below. It seems this is already more flawed then the simple formula shared above, but it too has three major flaws, one of which, deletion of the whole row that is referenced, I believe was more likely to happen then any other.

Here is the formula:

You would enter this in a cell like this:

Then you can use it in a INDIRECT formula like this:

Try it out and let me know what you think.