Today we revisit Episode 672. Rather than using the difficult syntax from that episode, Paul from Darby suggests using a named range. Episode 675 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today I want to take a look back to this Monday episode 672.
When I talked about setting up a Vlookup, and that VLOOKUP was looking up data on another worksheet.
I talked about how hard it is to create that syntax.
Never know if the apostrophes and the exclamation points come first or last.
I got a great note from Paul. Paul lives and Derby in the UK.
He said. You know hey, if you would set up a named range the whole thing is much much easier.
So let's take a look at that I'm going to go back to my worksheet here where the data is.
I'll select all of the data in my table.
Make sure that key of course is selected in the first column and then click right here in the name box.
The name Box is that drop down to the left of the formula bar, and I'll type a name there.
Now I can't have any spaces So maybe I can use something like MyData.
Hit "Enter".
And that's all it takes to set up a named range. So we now have a named range called MyData.
I'll go back to my original worksheet where I want to put the VLOOKUP in.
=VLOOKUP I want to look at cell A3. So $A3, now at this point, I just have to type the name of that name range.
I just did it a second ago, so I know it's MyData.
You know, but if it's a couple of days later I might forget so a great way to get a list of all of the named ranges in the worksheet is to press the F3 button.
F3 will bring up the "Paste name" box.
Basically, select the name, Click "OK" and I want the the second column and False.
So a great way to avoid having to enter the apostrophes, enter the exclamation points, just set up a named range back in the other worksheet.
Want to thank Paul for sending that idea in.
Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today I want to take a look back to this Monday episode 672.
When I talked about setting up a Vlookup, and that VLOOKUP was looking up data on another worksheet.
I talked about how hard it is to create that syntax.
Never know if the apostrophes and the exclamation points come first or last.
I got a great note from Paul. Paul lives and Derby in the UK.
He said. You know hey, if you would set up a named range the whole thing is much much easier.
So let's take a look at that I'm going to go back to my worksheet here where the data is.
I'll select all of the data in my table.
Make sure that key of course is selected in the first column and then click right here in the name box.
The name Box is that drop down to the left of the formula bar, and I'll type a name there.
Now I can't have any spaces So maybe I can use something like MyData.
Hit "Enter".
And that's all it takes to set up a named range. So we now have a named range called MyData.
I'll go back to my original worksheet where I want to put the VLOOKUP in.
=VLOOKUP I want to look at cell A3. So $A3, now at this point, I just have to type the name of that name range.
I just did it a second ago, so I know it's MyData.
You know, but if it's a couple of days later I might forget so a great way to get a list of all of the named ranges in the worksheet is to press the F3 button.
F3 will bring up the "Paste name" box.
Basically, select the name, Click "OK" and I want the the second column and False.
So a great way to avoid having to enter the apostrophes, enter the exclamation points, just set up a named range back in the other worksheet.
Want to thank Paul for sending that idea in.
Want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
