"Can there be Dynamic Ranges for VLOOKUP and Data Validation List for an Invoice?". Using the set up from Episode #1379, today's Duel adds Dynamic Validation and Dynamic VLOOKUP using OFFSET or INDEX. Let Mike and Bill show us how it's done!
Transcript of the video:
Hey, all right it's another dueling Excel podcast.
I'm Bill Jelen from MrExcel we'll joined by Mike Girvin from Excel Is Fun.
This is episode 75.
Dynamic Validation with Offset.
Hey, now last week we had the same problem.
Can there be dynamic ranges for VLOOKUP and Data Validation?
And Mike and I both used a table.
If you remember, we weren't feeling well last week.
Offset makes my head hurt.
So, I definitely wasn't, wasn't up for it can we have this Data Validation use the OFFSET function.
This is pretty wide you know that the Data Validation dialog will actually accept the OFFSET function.
So, you don't have to necessarily have it.
You know, I am another screen let's build this here. So, we start out =OFFSET(.
OFFSET has five arguments, the first argument is where do we start from the top left-hand corner.
So, I'll choose that cell their notice they already put the dollar signs in that's great.
How many rows down from there do we wanna go for the starting point 0, how many columns over from there we wanna go 0, how many rows tall, how many rows tall.
Okay well, I want this to grow and so, as we add more products.
I wanna come up with the complete list, I'm going to use COUNTA and I'm just gonna choose all of column G.
Now, we have to make sure they don't put anything else in column G.
Otherwise, you know we could kind of narrow that down a bit.
How many columns wide, one column wide. All right so, let's do all that, click OK.
All right so, right now our drop-down has product 1, 2, 3.
We come over here and add product 17 come back to the drop-down product 17 there we go it's working beautifully.
Okay how about the VLOOKUP can the VLOOKUP use that same OFFSET function.
So, right here instead of G6 to H28 can we use an OFFSET they row.
Of course you can that's the easier way to use an offset we're always going to start from this particular cell here so, again press F4 for the starting cell it is 0 rows down, 0 columns over, total number of rows tall that is the COUNTA of column G.
Probably, need in that for right there and for the number of columns wide, that is always gonna be two columns wide.
All right so, that offset is going to return an array which is like putting a range in here we should just press enter.
Copy that down.
Our VLOOKUP starts to work putting a place 49 yup good.
All right, Mike that's the offset way let's see what you have.
Thanks MrExcel.
Wow, you're over your sickness not me it's five weeks and I'm still just as sick as I was.
So, oh, we get to do Excel that always makes you feel better.
Oh, OFFSET great function here because it's specifically built to create dynamic ranges in essence.
However, the OFFSET does have one draw back, it is a volatile function which means it recalculates every time the sheet recalculates and that means even if there's no product change or no new records added, it'll recalculate.
So, for big spreadsheet sometimes this slows the calculating down.
So, in that case you can use the INDEX function to create a dynamic range.
Now, I'm going to go ahead and create my formula here and then apply it to the Data Validation list dialog box.
I'm gonna use the INDEX function.
However, INDEX can only look up in our case a single cell reference and so we start a formula like this, we click on G2 and then type of colon.
Now, we don't this, this is cell reference, colon, cell reference.
So, get rid of that one and actually we'll F4 this to lock it.
Now, here's the trick index usually looks up a value but if you put the INDEX function into the context of a cell reference, notice cell reference colon anything that comes after here it is expecting a cell reference.
So, if we put index here it will look up the cell reference instead of the actual value.
Now, the array MrExcel highlighted the whole column here.
I'm just gonna assume I know the maximum number of products i will have and highlight a few cells more and lock that that will be our lookup array, remember we're trying to find cell reference G8 in essence look up a cell reference comma the row number i'll use COUNTA and simply highlight that same range F4 COUNTA will give us three here which it the array, third row that's exactly what we want.
All right so, row number we don't need the column.
So, I just closed parenthesis now, watch this if I highlight this INDEX, and evaluate it.
What do you think it's going to deliver?
Well, of course the cell reference right F9 is evaluate no because it's not in the context of a cell reference yet it's by itself we evaluated it by itself control+Z, but now, if we highlight the whole thing and hit F9 boom.
It'll give us the range of values and that's a dynamic range, I'm gonna control+Z and I'm gonna enter this control+shift+enter just test it.
I'm gonna type in s, come up here evaluate F9.
Sure enough, we can see it's picking it up, control+Z.
Now, i'm gonna copy this because i'm gonna have to paint use this for, control+Z, control+C, copy, escape, delete that.
Now, if you try to come up here and put it in the source dialog box, it won't let you it doesn't like that colon there.
So, if you try it, it'll talk about unions and stuff like that I'm gonna click escape we have to use defined name and then put that define name in the Data Validation dialog box.
The keyboard shortcut for defined names or create new name, name manager is control+F3.
I'm gonna delete that the one.
I'm gonna click new and then give it some name like D, Data Validation, Dynamic Data Validation look up.
So, DDVL, come down here control+V, looks like I need an equal sign.
All right so, that range right there I'm gonna click OK and I'm gonna test it before I X out of this name manager, click right there and sure enough you see it's working.
All right close.
Now, I come and highlight this column Alt+D, tab L, tab L, actually could have just tab down there and get to that.
Now, I'm gonna oh, wait a second I forgot my name.
Oh, no problem F3 is paste name, DDL, I double click that and sure enough now, we even though that formula wasn't allowed here the defined name will work just fine.
I'm gonna click OK, I'm gonna test it real quick.
So, sure enough it's got a d.
Now, I'm going to put a value here 15 bucks or whatever it is apply some formatting maybe i'll just do product 4 like that.
All right so, now, I got oh yeah so, it does, does work there.
Now, we need to I don't didn't copy this all the way down did I not copy this one down that's what happens when you try to do this when you're 60.
We need to find a dynamic range using INDEX.
No problem I'm gonna actually copy this and change it, copy escape come right here and paste it and the only thing we need to do different because we're not looking up this cell reference, we need to look up this cell reference.
Well, first of I need to highlight from G6 to H6.
So, I'm going to change that now we got the whole table there and two arguments in INDEX row and column.
So, right now it knows to go down to row 4, but now we need to say column 2 well it's always 2 So, I can comma 2 and actually hard code that in that'll work right there.
If we highlight this and F9 perfect control+Z, copy control shift enter and then I'm going to come here and highlight that VLOOKUP a table array and control+V. I just pasted the index dynamic range there enter I sort of control enter a double click and send it down so we don't have any other so it looks like it's working just fine I'll say product 51 book let's see if this will work product five and that is so cool all right that's a index for dynamic ranges throw it back over to mr.
Excel like that is wild i never knew that you could use the index in a range name like that also the data validation validation dialog box and Ohio Governor John Kasich they both have something in common neither one of them likes unions and also f32 pace names there you I learned a lot in that episode well I want to thank everyone for stopping by we'll see you next week for another dueling excel podcast from mr. excel and excel is fun
I'm Bill Jelen from MrExcel we'll joined by Mike Girvin from Excel Is Fun.
This is episode 75.
Dynamic Validation with Offset.
Hey, now last week we had the same problem.
Can there be dynamic ranges for VLOOKUP and Data Validation?
And Mike and I both used a table.
If you remember, we weren't feeling well last week.
Offset makes my head hurt.
So, I definitely wasn't, wasn't up for it can we have this Data Validation use the OFFSET function.
This is pretty wide you know that the Data Validation dialog will actually accept the OFFSET function.
So, you don't have to necessarily have it.
You know, I am another screen let's build this here. So, we start out =OFFSET(.
OFFSET has five arguments, the first argument is where do we start from the top left-hand corner.
So, I'll choose that cell their notice they already put the dollar signs in that's great.
How many rows down from there do we wanna go for the starting point 0, how many columns over from there we wanna go 0, how many rows tall, how many rows tall.
Okay well, I want this to grow and so, as we add more products.
I wanna come up with the complete list, I'm going to use COUNTA and I'm just gonna choose all of column G.
Now, we have to make sure they don't put anything else in column G.
Otherwise, you know we could kind of narrow that down a bit.
How many columns wide, one column wide. All right so, let's do all that, click OK.
All right so, right now our drop-down has product 1, 2, 3.
We come over here and add product 17 come back to the drop-down product 17 there we go it's working beautifully.
Okay how about the VLOOKUP can the VLOOKUP use that same OFFSET function.
So, right here instead of G6 to H28 can we use an OFFSET they row.
Of course you can that's the easier way to use an offset we're always going to start from this particular cell here so, again press F4 for the starting cell it is 0 rows down, 0 columns over, total number of rows tall that is the COUNTA of column G.
Probably, need in that for right there and for the number of columns wide, that is always gonna be two columns wide.
All right so, that offset is going to return an array which is like putting a range in here we should just press enter.
Copy that down.
Our VLOOKUP starts to work putting a place 49 yup good.
All right, Mike that's the offset way let's see what you have.
Thanks MrExcel.
Wow, you're over your sickness not me it's five weeks and I'm still just as sick as I was.
So, oh, we get to do Excel that always makes you feel better.
Oh, OFFSET great function here because it's specifically built to create dynamic ranges in essence.
However, the OFFSET does have one draw back, it is a volatile function which means it recalculates every time the sheet recalculates and that means even if there's no product change or no new records added, it'll recalculate.
So, for big spreadsheet sometimes this slows the calculating down.
So, in that case you can use the INDEX function to create a dynamic range.
Now, I'm going to go ahead and create my formula here and then apply it to the Data Validation list dialog box.
I'm gonna use the INDEX function.
However, INDEX can only look up in our case a single cell reference and so we start a formula like this, we click on G2 and then type of colon.
Now, we don't this, this is cell reference, colon, cell reference.
So, get rid of that one and actually we'll F4 this to lock it.
Now, here's the trick index usually looks up a value but if you put the INDEX function into the context of a cell reference, notice cell reference colon anything that comes after here it is expecting a cell reference.
So, if we put index here it will look up the cell reference instead of the actual value.
Now, the array MrExcel highlighted the whole column here.
I'm just gonna assume I know the maximum number of products i will have and highlight a few cells more and lock that that will be our lookup array, remember we're trying to find cell reference G8 in essence look up a cell reference comma the row number i'll use COUNTA and simply highlight that same range F4 COUNTA will give us three here which it the array, third row that's exactly what we want.
All right so, row number we don't need the column.
So, I just closed parenthesis now, watch this if I highlight this INDEX, and evaluate it.
What do you think it's going to deliver?
Well, of course the cell reference right F9 is evaluate no because it's not in the context of a cell reference yet it's by itself we evaluated it by itself control+Z, but now, if we highlight the whole thing and hit F9 boom.
It'll give us the range of values and that's a dynamic range, I'm gonna control+Z and I'm gonna enter this control+shift+enter just test it.
I'm gonna type in s, come up here evaluate F9.
Sure enough, we can see it's picking it up, control+Z.
Now, i'm gonna copy this because i'm gonna have to paint use this for, control+Z, control+C, copy, escape, delete that.
Now, if you try to come up here and put it in the source dialog box, it won't let you it doesn't like that colon there.
So, if you try it, it'll talk about unions and stuff like that I'm gonna click escape we have to use defined name and then put that define name in the Data Validation dialog box.
The keyboard shortcut for defined names or create new name, name manager is control+F3.
I'm gonna delete that the one.
I'm gonna click new and then give it some name like D, Data Validation, Dynamic Data Validation look up.
So, DDVL, come down here control+V, looks like I need an equal sign.
All right so, that range right there I'm gonna click OK and I'm gonna test it before I X out of this name manager, click right there and sure enough you see it's working.
All right close.
Now, I come and highlight this column Alt+D, tab L, tab L, actually could have just tab down there and get to that.
Now, I'm gonna oh, wait a second I forgot my name.
Oh, no problem F3 is paste name, DDL, I double click that and sure enough now, we even though that formula wasn't allowed here the defined name will work just fine.
I'm gonna click OK, I'm gonna test it real quick.
So, sure enough it's got a d.
Now, I'm going to put a value here 15 bucks or whatever it is apply some formatting maybe i'll just do product 4 like that.
All right so, now, I got oh yeah so, it does, does work there.
Now, we need to I don't didn't copy this all the way down did I not copy this one down that's what happens when you try to do this when you're 60.
We need to find a dynamic range using INDEX.
No problem I'm gonna actually copy this and change it, copy escape come right here and paste it and the only thing we need to do different because we're not looking up this cell reference, we need to look up this cell reference.
Well, first of I need to highlight from G6 to H6.
So, I'm going to change that now we got the whole table there and two arguments in INDEX row and column.
So, right now it knows to go down to row 4, but now we need to say column 2 well it's always 2 So, I can comma 2 and actually hard code that in that'll work right there.
If we highlight this and F9 perfect control+Z, copy control shift enter and then I'm going to come here and highlight that VLOOKUP a table array and control+V. I just pasted the index dynamic range there enter I sort of control enter a double click and send it down so we don't have any other so it looks like it's working just fine I'll say product 51 book let's see if this will work product five and that is so cool all right that's a index for dynamic ranges throw it back over to mr.
Excel like that is wild i never knew that you could use the index in a range name like that also the data validation validation dialog box and Ohio Governor John Kasich they both have something in common neither one of them likes unions and also f32 pace names there you I learned a lot in that episode well I want to thank everyone for stopping by we'll see you next week for another dueling excel podcast from mr. excel and excel is fun
