All this week, Bill has been dealing with a Workbook with 31 sheets - one Worksheet per day. Today, in Episode #1490, Bill presents a bizarre Formula to put the Sheet Name in a Cell. Actually, today, I use a MID of the Sheet Name to fill in the correct Date in a Cell.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel for MrExcel, Episode 1490: Worksheet Name In Cell.
Well, Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
We kind of have a theme going on this week. Started out on Monday replicating 1 worksheet into 31 worksheets, and then, yesterday, how to rename all those.
Well, now I'd like to go one step further and I would like to be able to use the worksheet name in a formula so that way I could fill in that date somehow.
Now, this is always tough for me.
So, I am going to start out here.
Is it =INFO?
No, it's =CELL, =CELL, and I want the FILENAME.
Now, by the way, this only works if you've saved the file.
If the file is still up here with Book1 or Book2 like that, this doesn't return anything.
That's very important that you include that optional reference.
I'm just going to refer to the current cell G1.
If you don't do that, this is not going to work.
[ =CELL(“filename”,G1) ] So, what we get is we get the complete path file name and, check this out, the worksheet name.
So, you're always looking for that ] right there.
=FIND, the in “ the ] of that value, and that says that the first 43 characters are not the sheet name.
Everything after that is the sheet name.
Now, in this particular case, I just want to use that 1, so I'm going to say that I want to go 1, 2, 3, 4, 5 characters over from there, so I'm going to add +5, and I'm going to need either 1 or 2.
So, =MID of this value , starting there , 2 will get us a 1, and if we did the same thing on the 10th or 11th, they would get us the 2-digit number.
[ =FIND(“ ]”,G1)+5 ], [ =MID(G1,G2,2) ] So, now that I have that, here is what I'm trying to do.
I'm trying to get the date of 2000…it's 2011.
Had to think about that.
12 , that day and I'm betting, I’m betting, that I’m going to have to say +0 to convert this text up here into a number, alright?
So, there's our 12/1/2011.
Beautiful thing is, as we copy this to day 2, day 3, day 4, it will automatically update.
[ =DATE(2011,12,G3+0) ].
So, we are using this cool cell function to get the complete path and file name.
Don’t care about that, but the worksheet name, then using FIND to figure out where the worksheet name starts, then using MID to get just the piece of the worksheet name that we want, and then finally using that all in a formula.
Now, it is not necessary to include all 4 steps here.
We can do this all in one big step.
So, I take the G3, I go up F2 to put it in edit mode, and then copy everything except for the = sign, and select G3 here and paste, CONTROL+V. Alright.
So, now, I need to go get G2.
G2 is all of this except for the = sign, and right here, select the characters G2, CONTROL+V to paste.
Now, I have G1 left and I have it in 2 different places.
Alright.
Good enough.
Go up here and choose this formula except for the = sign, and, let's see, first here, G1, CONTROL+V, and here, G1, CONTROL+V. Alright.
Now, that final G1, that actually should be G4 because, again, I'm referring to the current cell, and that's just that reference.
If you don't put that reference in, it's funny, that cell gives you the answer for the last cell that you changed, and if that last cell that you just changed is on another worksheet then just chaos ensues, chaos ensues.
So, alright.
So, there’s my formula.
I’m going to copy that whole formula, CONTROL+C, and come back here where I want it to be, the DATE, then = sign, and two = signs.
Alright.
So, there's our formula.
[ =DATE(2011,12,MID(CELL(“filename”,G4),FIND(“ ]”,CELL(“filename”,G4))+5,2)+0) ] Now, let's just do a little test.
We will, first off, delete all this stuff over here, copy that formula, I'm going to do CONTROL+PAGEDOWN.
Now you see I'm on day 2, paste that in there.
That’s gorgeous.
So, we're actually looking at the sheet…worksheet name, getting the, basically, the 5th and 6th characters of the worksheet name, plugging it in the date cell to generate the current date.
I have to copy this to all 31.
Easy way to do that, I'm sitting on day 2, I’m going to press this icon to go to the last sheet, hold down SHIFT, and click DAY 31.
I've now put it in group mode.
I have every sheet from day 2 to day 31 selected and I'm looking at day 2, put this in edit mode, and then press ENTER to enter that formula in all of the cells of the group.
So, I'm going to right-click on group and we'll come back here, we'll just check it.
Look at that.
It copied the formula all the way across and the formula is working perfectly.
Alright.
So, we started out Monday with, hey, how do I make a copy of these 31 sheets, and I've been going on and on and on and on.
Sorry about that, but some good tricks here.
I love this one.
Very cool way to go.
Okay.
Well, hey.
I want to thank you for stopping by.
We'll see you next time another netcast from MrExcel.
Learn Excel for MrExcel, Episode 1490: Worksheet Name In Cell.
Well, Hey. Welcome back to the MrExcel netcast. I'm Bill Jelen.
We kind of have a theme going on this week. Started out on Monday replicating 1 worksheet into 31 worksheets, and then, yesterday, how to rename all those.
Well, now I'd like to go one step further and I would like to be able to use the worksheet name in a formula so that way I could fill in that date somehow.
Now, this is always tough for me.
So, I am going to start out here.
Is it =INFO?
No, it's =CELL, =CELL, and I want the FILENAME.
Now, by the way, this only works if you've saved the file.
If the file is still up here with Book1 or Book2 like that, this doesn't return anything.
That's very important that you include that optional reference.
I'm just going to refer to the current cell G1.
If you don't do that, this is not going to work.
[ =CELL(“filename”,G1) ] So, what we get is we get the complete path file name and, check this out, the worksheet name.
So, you're always looking for that ] right there.
=FIND, the in “ the ] of that value, and that says that the first 43 characters are not the sheet name.
Everything after that is the sheet name.
Now, in this particular case, I just want to use that 1, so I'm going to say that I want to go 1, 2, 3, 4, 5 characters over from there, so I'm going to add +5, and I'm going to need either 1 or 2.
So, =MID of this value , starting there , 2 will get us a 1, and if we did the same thing on the 10th or 11th, they would get us the 2-digit number.
[ =FIND(“ ]”,G1)+5 ], [ =MID(G1,G2,2) ] So, now that I have that, here is what I'm trying to do.
I'm trying to get the date of 2000…it's 2011.
Had to think about that.
12 , that day and I'm betting, I’m betting, that I’m going to have to say +0 to convert this text up here into a number, alright?
So, there's our 12/1/2011.
Beautiful thing is, as we copy this to day 2, day 3, day 4, it will automatically update.
[ =DATE(2011,12,G3+0) ].
So, we are using this cool cell function to get the complete path and file name.
Don’t care about that, but the worksheet name, then using FIND to figure out where the worksheet name starts, then using MID to get just the piece of the worksheet name that we want, and then finally using that all in a formula.
Now, it is not necessary to include all 4 steps here.
We can do this all in one big step.
So, I take the G3, I go up F2 to put it in edit mode, and then copy everything except for the = sign, and select G3 here and paste, CONTROL+V. Alright.
So, now, I need to go get G2.
G2 is all of this except for the = sign, and right here, select the characters G2, CONTROL+V to paste.
Now, I have G1 left and I have it in 2 different places.
Alright.
Good enough.
Go up here and choose this formula except for the = sign, and, let's see, first here, G1, CONTROL+V, and here, G1, CONTROL+V. Alright.
Now, that final G1, that actually should be G4 because, again, I'm referring to the current cell, and that's just that reference.
If you don't put that reference in, it's funny, that cell gives you the answer for the last cell that you changed, and if that last cell that you just changed is on another worksheet then just chaos ensues, chaos ensues.
So, alright.
So, there’s my formula.
I’m going to copy that whole formula, CONTROL+C, and come back here where I want it to be, the DATE, then = sign, and two = signs.
Alright.
So, there's our formula.
[ =DATE(2011,12,MID(CELL(“filename”,G4),FIND(“ ]”,CELL(“filename”,G4))+5,2)+0) ] Now, let's just do a little test.
We will, first off, delete all this stuff over here, copy that formula, I'm going to do CONTROL+PAGEDOWN.
Now you see I'm on day 2, paste that in there.
That’s gorgeous.
So, we're actually looking at the sheet…worksheet name, getting the, basically, the 5th and 6th characters of the worksheet name, plugging it in the date cell to generate the current date.
I have to copy this to all 31.
Easy way to do that, I'm sitting on day 2, I’m going to press this icon to go to the last sheet, hold down SHIFT, and click DAY 31.
I've now put it in group mode.
I have every sheet from day 2 to day 31 selected and I'm looking at day 2, put this in edit mode, and then press ENTER to enter that formula in all of the cells of the group.
So, I'm going to right-click on group and we'll come back here, we'll just check it.
Look at that.
It copied the formula all the way across and the formula is working perfectly.
Alright.
So, we started out Monday with, hey, how do I make a copy of these 31 sheets, and I've been going on and on and on and on.
Sorry about that, but some good tricks here.
I love this one.
Very cool way to go.
Okay.
Well, hey.
I want to thank you for stopping by.
We'll see you next time another netcast from MrExcel.
