Knowing how to get a sheet name in excel could prove very useful when preparing your models. For example, during my preparation of a 10 year business plan that was to undergo model audit process because its purpose was to raise capital for expansion, I was asked by the CEO to link all assumptions and references to a single reference worksheet, and then have a link bank from each worksheet at the exact point the assumption was used.
Meaning that he wanted to be able to simply click a link from any worksheet and be taking to the reference worksheet, and then click another link on the same row in the reference worksheet and be taken back to the original worksheet he came from. To do this, I needed an in depth understanding of creating dynamic hyperlinks in excel and the first step was to know how to extract a worksheets name.
In this post, we would take you through easy steps to getting a sheet name in excel and in subsequent posts. We would go deep into creating dynamic hyperlinks to serve purposes such as dynamic references as discussed in the previous paragraph. Let’s begin shall we.
Function for Getting Sheet Name
Getting the sheet name of any worksheet is as easy as typing a function in any cell within the worksheet. The function to type can be seen below
=MID(CELL("filename",A1), FIND("]",CELL("filename",A1))+1,255)Please note that you don’t need to refer to A1 in the formula. You can refer to any other cell within the worksheet and it would still work fine.
Breaking it Down
The starting point for excel for the function discussed in the previous section is to get the filename after which it can now filter down to the exact worksheet name using the MID function. The cell function when used returns the full path and file name. Just copying and pasting the Cell(“Filename”,A1) should give you the full path and file name.
=CELL("filename",A1)
The result of the formula above would return something like this: path[workbook.xlsm]sheetname.
Notice that in the first function, this Cell(“filename”,A1) formula is repeated twice. This is because it uses the MID function to identify the starting point of the worksheet name.
From the image above, you can see that the MID function has 3 sections. But before we explain these sections, please note that the purpose of the MID function is to extract a particular portion of a text based on what the user defines. Back to it then, the first section is the Full Text you are trying to fragment. In this case, the full text is the full path and file name.
The next section “Start num” is how many characters to the right excel needs to register the first letter in the portion of the text you are really interested in.
In our case, the First section (text) is the full path and file name since we are using the function:
Cell("filename",A1)
The Second section (start num) uses the function:
FIND("]"Cell("filename",A1)+1
Because the worksheet name begins just after ], we add a +1 in order for excel to start capturing from the first letter in the word that starts after [workbook.xlsm].
Finally, we have num_chars as the last section. You can see that it is hardcoded to 255. This is because it is the longest number of characters permitted as a worksheet name, so it ensures that the full worksheet name is captured whatever the case may be.
