Name Box Expand is a great way to actually be able to see your Ranges - as you've named them - in the Named Range Box, without trying to guess what you are looking at. But did you know that you can find that name showing in lists when using other Functions of Excel? Bill shows us an interesting discovery and the uses of that discovery in Episode #1579 - this may make your Excel tasks just that much easier and more logical to follow. Today begins a week-long Series on Named Ranges: This is Part I of IV
This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
This Excel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast: name box expand an implicit intersection.
Hey welcome back to the MrExcel netcast I'm Bill Jelen, I'm working on those new books for Excel 2013, I ran across a cool trick.
First thing we're going to do here is create some names from selection now you've seen Mike Girvin do this, top row, left column, click ok and what that's done for me it's to find a whole bunch of names like for example this range here is called week underscore one this range here is called Cincinnati, oh well we can't see what it's called and that's actually the trick that I learned.
So it's kind of frustrating when the name box isn't big enough to see what you want to see there because the name is too long especially like down here and Des Moines Washington, oh I can't see it at all came and tell the difference between that and Des Moines Iowa, but check this out, that little circle right there hey did you know that's a handle, you can hover over that circle and make the name box bigger, makes the formula bar smaller, name box bigger.
So now for those really long names you have, you can see what's going on.
All right, so how you know one trick that I'm sure that I've shown on the podcast before is where we can kind of do a two way look up I can look for Cleveland Ohio space week one and it will give me the intersection of those two so I've talked about that before but I didn't realize that this trick would work if I, see that was outside on the range, if I come down here below the range and I just asked for Cleveland Ohio it is going to give me the 1756 from cell B3.
It's called implicit intersection it says hey this cell is in the same columns as that range Cleveland Ohio and so it's going to give me just the value that intersects with that column same thing over here I can come along and ask for example equally two or even let's do a week 2 plus week 3 and it will give me the SUM of these two 27+29 and then I copy it down it gives me the SUM of the next two, the next two, the next two.
Now if I copy it outside of the range has no clue what to do with that at all but it so it only works when you're within the rows or columns that intersect that range I thought that was really cool in fact, so this is called implicit intersection, you can even do something really bizarre like equal this range over here F4 and because I'm intersecting that range D2 intersects B2, I’m going to get the answer from B2 and as I copy it down I'm going to continue to get the answers but down here it gives me the value error did you ever know that that's wild I learn something new, got to share it with you.
Okay hey, I want to thank for stopping by see you next time for another netcast by MrExcel.
Learn Excel from MrExcel podcast: name box expand an implicit intersection.
Hey welcome back to the MrExcel netcast I'm Bill Jelen, I'm working on those new books for Excel 2013, I ran across a cool trick.
First thing we're going to do here is create some names from selection now you've seen Mike Girvin do this, top row, left column, click ok and what that's done for me it's to find a whole bunch of names like for example this range here is called week underscore one this range here is called Cincinnati, oh well we can't see what it's called and that's actually the trick that I learned.
So it's kind of frustrating when the name box isn't big enough to see what you want to see there because the name is too long especially like down here and Des Moines Washington, oh I can't see it at all came and tell the difference between that and Des Moines Iowa, but check this out, that little circle right there hey did you know that's a handle, you can hover over that circle and make the name box bigger, makes the formula bar smaller, name box bigger.
So now for those really long names you have, you can see what's going on.
All right, so how you know one trick that I'm sure that I've shown on the podcast before is where we can kind of do a two way look up I can look for Cleveland Ohio space week one and it will give me the intersection of those two so I've talked about that before but I didn't realize that this trick would work if I, see that was outside on the range, if I come down here below the range and I just asked for Cleveland Ohio it is going to give me the 1756 from cell B3.
It's called implicit intersection it says hey this cell is in the same columns as that range Cleveland Ohio and so it's going to give me just the value that intersects with that column same thing over here I can come along and ask for example equally two or even let's do a week 2 plus week 3 and it will give me the SUM of these two 27+29 and then I copy it down it gives me the SUM of the next two, the next two, the next two.
Now if I copy it outside of the range has no clue what to do with that at all but it so it only works when you're within the rows or columns that intersect that range I thought that was really cool in fact, so this is called implicit intersection, you can even do something really bizarre like equal this range over here F4 and because I'm intersecting that range D2 intersects B2, I’m going to get the answer from B2 and as I copy it down I'm going to continue to get the answers but down here it gives me the value error did you ever know that that's wild I learn something new, got to share it with you.
Okay hey, I want to thank for stopping by see you next time for another netcast by MrExcel.
