Ed asks how to assign a list of weekdays to a name in Excel. Episode 1156 shows you how.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Look, yes, it's Friday, and yes, this should be a Dueling podcast, but things have been so crazy around here.
Didn't have a chance to get set up with Mike to do the Dueling podcast this week, so you're stuck with just me.
Hey, check this out, I love this, this has just arrived yesterday, it's the classic bubble tumbler with the MrExcel logo.
But even better, we've added the MrExcel tag cloud, 100 Excel terms going around there, and I don’t know if you can see it, if you know all of those terms, you are officially an Excel Guru.
Now, if you're the kind of person who says “I need desperately need a MrExcel bubble tumbler on my desk.”, just shoot me a note, bill@mrexcel.com.
If you're in the US, if you're one of the first 10 people to write in today, I'll be happy to send one of these out to you, share the joy, see if you know everything there.
Hey, today's question comes in from Ed, Ed asked a great question, I think I've answered something like this before.
In fact Mike Girvin has shown us how to do this before.
But let's take a quick look, it said “Hey, I have an INDEX function that is going out and looking through a range, like, for example, a range of weekdays, and I need to return the 5th week data to get Friday.” He says “Is there some way to do this without putting the weekdays in the sheet?
Can we like embed it as a named range?” is what he actually said.
And so, when we're in Edit mode here, we can actually choose that reference, so choose the reference, and then press the F9 key.
F9 is going to take that reference and convert it into what's known as array syntax.
So in array syntax we have semicolons between each row, curly brackets around the whole thing.
Now, I'm going to press Ctrl+C to copy just those characters from onto the clipboard.
Then we go into Formulas, Name Manager, or Define Name, let's define a name, and we'll call it Weekdays.
And here, we're going to leave the equal sign, and then press Ctrl+V to paste, click OK, alright.
So now, we should be able to do =INDEX of Weekdays, ,2 , and get the 2nd week date.
Isn't that cool?
Excellent!
Alright well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
Basically, you start out with massive amounts of data, and say “How we're going to analyze this?” Well, let's fire up a Pivot table and see if we can solve this problem!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Look, yes, it's Friday, and yes, this should be a Dueling podcast, but things have been so crazy around here.
Didn't have a chance to get set up with Mike to do the Dueling podcast this week, so you're stuck with just me.
Hey, check this out, I love this, this has just arrived yesterday, it's the classic bubble tumbler with the MrExcel logo.
But even better, we've added the MrExcel tag cloud, 100 Excel terms going around there, and I don’t know if you can see it, if you know all of those terms, you are officially an Excel Guru.
Now, if you're the kind of person who says “I need desperately need a MrExcel bubble tumbler on my desk.”, just shoot me a note, bill@mrexcel.com.
If you're in the US, if you're one of the first 10 people to write in today, I'll be happy to send one of these out to you, share the joy, see if you know everything there.
Hey, today's question comes in from Ed, Ed asked a great question, I think I've answered something like this before.
In fact Mike Girvin has shown us how to do this before.
But let's take a quick look, it said “Hey, I have an INDEX function that is going out and looking through a range, like, for example, a range of weekdays, and I need to return the 5th week data to get Friday.” He says “Is there some way to do this without putting the weekdays in the sheet?
Can we like embed it as a named range?” is what he actually said.
And so, when we're in Edit mode here, we can actually choose that reference, so choose the reference, and then press the F9 key.
F9 is going to take that reference and convert it into what's known as array syntax.
So in array syntax we have semicolons between each row, curly brackets around the whole thing.
Now, I'm going to press Ctrl+C to copy just those characters from onto the clipboard.
Then we go into Formulas, Name Manager, or Define Name, let's define a name, and we'll call it Weekdays.
And here, we're going to leave the equal sign, and then press Ctrl+V to paste, click OK, alright.
So now, we should be able to do =INDEX of Weekdays, ,2 , and get the 2nd week date.
Isn't that cool?
Excellent!
Alright well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Well thanks for stopping by, we'll see you next time for another netcast from MrExcel!
