Today's Dueling Podcast asks: "Can there be Dynamic Ranges for VLOOKUP and Data Validation List for an Invoice?" In Episode #1379, Mike "ExcelIsFun" Girvin and Bill "MrExcel" Jelen look at using Table Nomenclature in Validation and in VLOOKUP.
Transcript of the video:
Bill: Hey welcome back it’s another Dueling Excel podcast I'm Bill Jelen from MrExcel and we will be joined by Mike Girvin from Excel Is Fun.
This is episode 74DVV: Dynamic Validation and VLOOKUP.
Hey, all right, Mike back from Florida and I'm back from Florida.
I spent most of the time in Florida sick.
I hear that you're not feeling well either so we'll see.
This is a hard topic when you are battling whatever it is that we have so good luck and actually for the folks who have been watching my podcast all week we kind of have some tough topics, so here we go.
Let's go through it.
Question, “Can there be dynamic ranges for VLOOKUP and Data Validation list for an invoice?” So here we want to have little data validation lookups but rather than hard coding to this list as we add more items to the list we want that to grow.
All right, so of course you can use offset and you can do all kinds of things like that but now that we're in Excel 2007 or 2010 it is not necessary to do that.
We can just create a table from this and the beautiful thing about a table is formulas that point to the table will automatically extend.
Ctrl+T for a table, my table has headers yep but then I'm going to come up here and change Table 1, that name that they give it to something better.
I'm going to call it PT for product table.
Now when I need to refer to that later I'm going to remember that table name.
All right, so first thing; the VLOOKUP here so I'm going to come back and edit that VLOOKUP and now that table has been created and when I point to the table and say =IF(C5=””,””, VLOOKUP(C5,PT,2,FALSE)) and copy that formula down All right, check this out.
The name of the table, it's almost like a named range but it's even better than a named range because as I add new products, product 4 and product 5, those automatically become incorporated in the table.
See the table formatting groups?
So that's a really nice improvement with Excel 2007.
I don't have to change these formulas at all.
It just continues to work.
Now let's take a look at the data validation in here.
So I”ll do Alt+D+L. This shortcut is a good way to do data validation.
You see they've hardcoded this to a specific list and that's not what we're going to use.
I'm going to use the equal indirect and inside of indirect I’m going to put the name of that table.
The name the table is PT and then in square brackets the name of the column which is product.
The formula will be =INDIRECT(PT[ Product ]) All right so this is a table nomenclature.
A formula that says that we are going to look for just the PT table and the product field within that table.
Now that needs to be in quotes here.
So we are going to put quotes around everything inside of the indirect because the INDIRECT is looking for some text that evaluates these changes to other cells.
The formula will be =INDIRECT(“PT[ Product ]”) All right, so let's try it now that we have changed the validation.
I will add a product 5 for $66 and come back here sure enough the new items are automatically added to the bottom of the table.
Isn't that sweet?
All right, so if you're back in Excel 2003 yes you could use offset and dynamic ranges but if you're in Excel 2007 or newer and you're not going to be sharing the workbook with someone that has Excel 2003 or earlier, this table nomenclature, Ctrl+T, name the table is a great way to go.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Yes, I hope you get well soon.
It's no fun being sick.
I've been sick for almost four weeks now.
Now, I got to love the method you used, the Table feature.
2007-10, it's the way to go even in 2003 you can use the list feature.
It's so cool in fact I think I'm going to do a slight variation here using the table feature.
Now before we convert this to a table I actually have to add field names up here.
So this will be “Product” and this one is “Price.” Let's go take a look at the data validation and what is in the dialog box Alt+D+L. You can see the range $G$6:$G:$8 right there.
I'm going to click OK and let's look at our VLOOKUP table.
$G$6:$G:$8.
Now if they built this correctly when we convert this to a table those ranges should automatically update.
Let's just check it out and see if it works.
So I'm going to Ctrl+T and click OK.
So now we have a table and now we have these dynamic ranges.
We could see Table 3 and that's the name by default and I'm just going to accept that.
Let's click here and Alt+D+L. Oh it's still $G$6:$G:$8 and here nothing changed.
We don't see that table nomenclature that MrExcel used.
Now let's just see what happens.
I'm going to click right here and tab to add a new record, Product 4 and then let's say $10.
Let's go over here and look at this drop down.
Would you believe it; it absolutely worked.
Let's click here and Alt+D+L, look at that $G$6:$G:$9.
So that range even though it was not a table when we put it in recognizes that we created an official table there.
So the range is dynamic, but what about the lookup table?
It doesn't.
Why in the world and I actually don't know the reason, maybe MrExcel does or someone else.
Why did the data validation dialog box recognize when we converted a table but not VLOOKUP?
Now, we could just highlight this range here and it would do just as MrExcel did in his and it would show you the table formula nomenclature.
Now that's really cool stuff especially if you building formulas on other sheets but I don't want that here I want to have that range.
So I'm going to type it in.
=IF(C5=””,””, VLOOKUP(C5, $G$6:$H$9,2,0)) I'm going to hit F4 each time.
Now, for whatever reason, now that the table is a table and we highlighted it, it will work inside a VLOOKUP but you have to go through that extra step so I sent it down.
Now let's add a new record, I'll do the MrExcel method here.
Product 5 and then 15 let's say.
Let's select the drop-down and it did pick up and sure enough the H9 moved to H10.
So for some reason there's a disconnect between these two.
Most formulas that I have made that are just cell references do update but for whatever reason this VLOOKUP doesn't.
All right, I'll throw it back to MrExcel.
Bill: So Mike that's a really interesting discovery because we have, you know Microsoft dealing with the table nomenclature in many different ways.
PivotTables will use the table nomenclature and automatically grow but charts which will automatically grow do not support the table nomenclature.
I actually talked to someone at Microsoft, one of the Excel project managers and I said, “you know, why doesn't the series formula respect the table nomenclature or allow it?
“and he said you know at the time that they were creating tables first of all “we figured it would be too small of a population that would want to use the table nomenclature up there and we were afraid that it was going to break too many other things” and think about it that's when they were rewriting the chart engine for Excel 2007.
He says now “at this point we are focused on other areas trying to make sure it's easier for the beginning Excel user.” So I don't think they'll ever go back do this, so data validation is one that automatically grows but does not apparently support the table nomenclature so again similar decision must have been made there.
Let's make a great poster on the wall and write things that do and things that don't support the table nomenclature but still will continue to work even though it won't.
All right, so hey fascinating foray into the table nomenclature and data validation.
I want to thank you for stopping by.
We’ll see you next time for another duelling podcast from Excel is Fun and MrExcel.
This is episode 74DVV: Dynamic Validation and VLOOKUP.
Hey, all right, Mike back from Florida and I'm back from Florida.
I spent most of the time in Florida sick.
I hear that you're not feeling well either so we'll see.
This is a hard topic when you are battling whatever it is that we have so good luck and actually for the folks who have been watching my podcast all week we kind of have some tough topics, so here we go.
Let's go through it.
Question, “Can there be dynamic ranges for VLOOKUP and Data Validation list for an invoice?” So here we want to have little data validation lookups but rather than hard coding to this list as we add more items to the list we want that to grow.
All right, so of course you can use offset and you can do all kinds of things like that but now that we're in Excel 2007 or 2010 it is not necessary to do that.
We can just create a table from this and the beautiful thing about a table is formulas that point to the table will automatically extend.
Ctrl+T for a table, my table has headers yep but then I'm going to come up here and change Table 1, that name that they give it to something better.
I'm going to call it PT for product table.
Now when I need to refer to that later I'm going to remember that table name.
All right, so first thing; the VLOOKUP here so I'm going to come back and edit that VLOOKUP and now that table has been created and when I point to the table and say =IF(C5=””,””, VLOOKUP(C5,PT,2,FALSE)) and copy that formula down All right, check this out.
The name of the table, it's almost like a named range but it's even better than a named range because as I add new products, product 4 and product 5, those automatically become incorporated in the table.
See the table formatting groups?
So that's a really nice improvement with Excel 2007.
I don't have to change these formulas at all.
It just continues to work.
Now let's take a look at the data validation in here.
So I”ll do Alt+D+L. This shortcut is a good way to do data validation.
You see they've hardcoded this to a specific list and that's not what we're going to use.
I'm going to use the equal indirect and inside of indirect I’m going to put the name of that table.
The name the table is PT and then in square brackets the name of the column which is product.
The formula will be =INDIRECT(PT[ Product ]) All right so this is a table nomenclature.
A formula that says that we are going to look for just the PT table and the product field within that table.
Now that needs to be in quotes here.
So we are going to put quotes around everything inside of the indirect because the INDIRECT is looking for some text that evaluates these changes to other cells.
The formula will be =INDIRECT(“PT[ Product ]”) All right, so let's try it now that we have changed the validation.
I will add a product 5 for $66 and come back here sure enough the new items are automatically added to the bottom of the table.
Isn't that sweet?
All right, so if you're back in Excel 2003 yes you could use offset and dynamic ranges but if you're in Excel 2007 or newer and you're not going to be sharing the workbook with someone that has Excel 2003 or earlier, this table nomenclature, Ctrl+T, name the table is a great way to go.
Mike, let's see what you have.
Mike: Thanks MrExcel.
Yes, I hope you get well soon.
It's no fun being sick.
I've been sick for almost four weeks now.
Now, I got to love the method you used, the Table feature.
2007-10, it's the way to go even in 2003 you can use the list feature.
It's so cool in fact I think I'm going to do a slight variation here using the table feature.
Now before we convert this to a table I actually have to add field names up here.
So this will be “Product” and this one is “Price.” Let's go take a look at the data validation and what is in the dialog box Alt+D+L. You can see the range $G$6:$G:$8 right there.
I'm going to click OK and let's look at our VLOOKUP table.
$G$6:$G:$8.
Now if they built this correctly when we convert this to a table those ranges should automatically update.
Let's just check it out and see if it works.
So I'm going to Ctrl+T and click OK.
So now we have a table and now we have these dynamic ranges.
We could see Table 3 and that's the name by default and I'm just going to accept that.
Let's click here and Alt+D+L. Oh it's still $G$6:$G:$8 and here nothing changed.
We don't see that table nomenclature that MrExcel used.
Now let's just see what happens.
I'm going to click right here and tab to add a new record, Product 4 and then let's say $10.
Let's go over here and look at this drop down.
Would you believe it; it absolutely worked.
Let's click here and Alt+D+L, look at that $G$6:$G:$9.
So that range even though it was not a table when we put it in recognizes that we created an official table there.
So the range is dynamic, but what about the lookup table?
It doesn't.
Why in the world and I actually don't know the reason, maybe MrExcel does or someone else.
Why did the data validation dialog box recognize when we converted a table but not VLOOKUP?
Now, we could just highlight this range here and it would do just as MrExcel did in his and it would show you the table formula nomenclature.
Now that's really cool stuff especially if you building formulas on other sheets but I don't want that here I want to have that range.
So I'm going to type it in.
=IF(C5=””,””, VLOOKUP(C5, $G$6:$H$9,2,0)) I'm going to hit F4 each time.
Now, for whatever reason, now that the table is a table and we highlighted it, it will work inside a VLOOKUP but you have to go through that extra step so I sent it down.
Now let's add a new record, I'll do the MrExcel method here.
Product 5 and then 15 let's say.
Let's select the drop-down and it did pick up and sure enough the H9 moved to H10.
So for some reason there's a disconnect between these two.
Most formulas that I have made that are just cell references do update but for whatever reason this VLOOKUP doesn't.
All right, I'll throw it back to MrExcel.
Bill: So Mike that's a really interesting discovery because we have, you know Microsoft dealing with the table nomenclature in many different ways.
PivotTables will use the table nomenclature and automatically grow but charts which will automatically grow do not support the table nomenclature.
I actually talked to someone at Microsoft, one of the Excel project managers and I said, “you know, why doesn't the series formula respect the table nomenclature or allow it?
“and he said you know at the time that they were creating tables first of all “we figured it would be too small of a population that would want to use the table nomenclature up there and we were afraid that it was going to break too many other things” and think about it that's when they were rewriting the chart engine for Excel 2007.
He says now “at this point we are focused on other areas trying to make sure it's easier for the beginning Excel user.” So I don't think they'll ever go back do this, so data validation is one that automatically grows but does not apparently support the table nomenclature so again similar decision must have been made there.
Let's make a great poster on the wall and write things that do and things that don't support the table nomenclature but still will continue to work even though it won't.
All right, so hey fascinating foray into the table nomenclature and data validation.
I want to thank you for stopping by.
We’ll see you next time for another duelling podcast from Excel is Fun and MrExcel.
