Rupal asks how to return the worksheet name as the result of a formula. Episode 452 shows the obscure function which can be adapted to solve this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm bill Jelen.
Today, we have a question was asked by Rupel, in one of my Excel seminars.
This is a question that drove me crazy in the seminar because I knew there was some way to solve the problem.
But, I couldn't remember the obscure function and this is also a netcast where I bet, that I'll get email from several people saying, "Hey Bill!
There's a better way to do that." Feel free to drop me an email.
The question that Rupel had, was she had a worksheet with all the months going across the worksheet tabs, and she'd love to have a way, a function in cell A1, that would return the name of the tab.
So, that way if she's named the tabs, the months January, Feb, March, April, May, June.
Basically, that function will pull the name of the tab and she won't have to change the worksheets all the time to re-enter the information.
I knew that there was a way to do this.
I couldn't remember it on the spot, turns out that there is an old Lotus 123 function called cell.
In the cell function you can basically ask for any one of 10 different types of information about any particular cell.
Now, we don't care what cell it is.
So, in this case I'm just going to use cell A1, but what I'm going to ask for is the filename and the strange thing about the file name is that it returns us the complete path to the file.
The name of the file in square brackets.
I have no idea, why they use square brackets?
And then after that the name of the worksheet.
So, this kind of gets us close.
Now, to actually solve the problem.
We need to isolate just the name of the worksheet.
My example back here in February, my first tab at this was...
If all of your worksheet names happen to be exactly three characters long, we could just ask for the rightmost three characters, using the RIGHT function.
So, equal right (=RIGHT), cell filename A1 and then ask for three characters, and that'll work fine.
Now, that's kind of cheating and it assumes that all of your worksheets have the same name.
What if someone comes along and renames, Jan to January.
This is much harder to solve unfortunately my first step was I said, okay Now, let's find that right square bracket.
So, use the FIND function to find the right square bracket and find out that that is at position 64.
Next thing I did was use a length function, LEN to find out how long the entire path filename and worksheet name is.
In this case, It's 71.
I then jammed all those together into one big huge formula, that basically says we're gonna take the right most characters from the function and then uses the length function and the find function to put all that together into one big string Now, the great thing about this, is that cell A1 is a formula.
If I type anything it will automatically update with the information.
Now, once we've entered that formula in one sheet.
There's a great trick to enter the formula in all of the sheets.
I have January selected.
I'm going to [ shift ] click on the last worksheet, that puts me in group mode.
I'll edit cell A1 and then hit [ enter ] and sure enough.
We should see that all of the worksheets now, have that formula showing us the worksheet name.
That was a great question, from Rupel.
Using the old cell function from Lotus 123 is one way to solve this.
I know that you could use VBA, a quick VBA function.
And if you have any better way of solving this problem, please drop me a note, Bill@MrExcel.com.
and we'll show it on a future podcast.
Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
I'm bill Jelen.
Today, we have a question was asked by Rupel, in one of my Excel seminars.
This is a question that drove me crazy in the seminar because I knew there was some way to solve the problem.
But, I couldn't remember the obscure function and this is also a netcast where I bet, that I'll get email from several people saying, "Hey Bill!
There's a better way to do that." Feel free to drop me an email.
The question that Rupel had, was she had a worksheet with all the months going across the worksheet tabs, and she'd love to have a way, a function in cell A1, that would return the name of the tab.
So, that way if she's named the tabs, the months January, Feb, March, April, May, June.
Basically, that function will pull the name of the tab and she won't have to change the worksheets all the time to re-enter the information.
I knew that there was a way to do this.
I couldn't remember it on the spot, turns out that there is an old Lotus 123 function called cell.
In the cell function you can basically ask for any one of 10 different types of information about any particular cell.
Now, we don't care what cell it is.
So, in this case I'm just going to use cell A1, but what I'm going to ask for is the filename and the strange thing about the file name is that it returns us the complete path to the file.
The name of the file in square brackets.
I have no idea, why they use square brackets?
And then after that the name of the worksheet.
So, this kind of gets us close.
Now, to actually solve the problem.
We need to isolate just the name of the worksheet.
My example back here in February, my first tab at this was...
If all of your worksheet names happen to be exactly three characters long, we could just ask for the rightmost three characters, using the RIGHT function.
So, equal right (=RIGHT), cell filename A1 and then ask for three characters, and that'll work fine.
Now, that's kind of cheating and it assumes that all of your worksheets have the same name.
What if someone comes along and renames, Jan to January.
This is much harder to solve unfortunately my first step was I said, okay Now, let's find that right square bracket.
So, use the FIND function to find the right square bracket and find out that that is at position 64.
Next thing I did was use a length function, LEN to find out how long the entire path filename and worksheet name is.
In this case, It's 71.
I then jammed all those together into one big huge formula, that basically says we're gonna take the right most characters from the function and then uses the length function and the find function to put all that together into one big string Now, the great thing about this, is that cell A1 is a formula.
If I type anything it will automatically update with the information.
Now, once we've entered that formula in one sheet.
There's a great trick to enter the formula in all of the sheets.
I have January selected.
I'm going to [ shift ] click on the last worksheet, that puts me in group mode.
I'll edit cell A1 and then hit [ enter ] and sure enough.
We should see that all of the worksheets now, have that formula showing us the worksheet name.
That was a great question, from Rupel.
Using the old cell function from Lotus 123 is one way to solve this.
I know that you could use VBA, a quick VBA function.
And if you have any better way of solving this problem, please drop me a note, Bill@MrExcel.com.
and we'll show it on a future podcast.
Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
