Fred used Data Validation to select a model from the list. Once you select the model, how do you pull all of the field data forward to Sheet1? There are many approaches you could use. Episode 1803 shows how to use MATCH and INDEX.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1803: Grab the Selected Record.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question sent by Fred.
Can you help me with a formula the spreadsheet.
I created a drop-down a model number and I in A2, from sheet 2 and sure enough, yes data validation, set up there that's grabbing the model numbers from sheet 2.
So, we can choose from the list.
All right, and then what I'm trying to do is want a select a model.
Then put that information from the table on sheet2 into these various cells and what he's done is that =IF (A2=Sheet2!
A3, Sheet2!
C3) the first record the first model, then grabs the value from sheet2 C3 and this is all working great if we happen to choose the first model in the list.
But, if we don't choose the first model if we choose the second model in other words=IF(A2=A3) on sheet2 then we're getting all false is here and Fred realized that he's started down a path that would get really, really unwieldy very quickly.
He would have to do something like here in the value of false do another F statement IF (A2=Sheet2!
A4, Sheet2!
C4 and soon you'd have to do this putting like 55 different IF statements together which of course doesn't work.
Yeah, more than 32 it's just it's an unwieldy way to go.
All right, so let's use a couple of functions that will help with this the first thing I want to know is, I want to know which row did we select?
So, we selected item what row is that on and to do that we're gonna use =MATCH the match function will look for a value the selected value comma in this column and I'm going to choose the whole column here we don't have to choose the goal column I am and comma and then 0 because we want an exact match press ENTER and it says hey you chose an item from Row 3 let's do something else you chose an item from row 6 you chose an item from row 11 alright and so that is going to help in the formulas that follow.
All right, I'm going to use the same trick here to figure out which column we want to pull the data in.
So, these are various columns and so here I'm going to use match again =MATCH(A4, of voltage comma in Row one.
This time I'm going to press the F4 key here because I'm going to be copying this form of the down the sheet and I want to make sure I always want to row one that puts the dollar signs in see $1 $1,0 and what's up with that voltage.
All right, so we see that voltage heading there.
I see the voltage heading here for some reason is not singing match.
I'm going to press F2 and there's no space after the VOLTAGE you can see the flashing insertion point and come back to sheet1 and press F2 there's a space.
Okay, so, let's get rid of that space that allows me to match function to work all right we'll copy it down here.
All right, what's up here is our space no space.
Okay, so, here we have a Dash here we have a Slash.
All right, so, to make life easier friend these headings here should match the headings on sheet2 but they don't and that's okay, they don't have to there's it's not like we have a million of these we can just kind of go through here and see that hey, we're getting this from C3, C is the third column so i'll put a 3 there.
Here we're getting the value from D3 that's the 4th column, I'll put a 4 here.
Then E3, I'll put a 5 because E is the 5th column, 6th column, G is the 7th column, H is the 8th column and then J the 10th column.
So, I don't know what happened to whatever was in column 9.
But, for whatever reason this is what Fred wants to pull back so we now know the row number up here in the column number.
Now that we know that great little function called index =INDEX(answers,$B$2,B5) and we're going to give it an array of answers and come back for a second and then specify what row number alright the road numbers up here in B2.
I always want to grab the row number from being too, so I'm going to press F4 there to put the dollar sign be $2 comma and then which column number that's the number directly to the right of me closing parenthesis all right answers boy we could use a named range and just leave answers there but I'm not going to complicate things right now.
Therefore, the array I'm going to click back to sheet 2.
I'm going to start up here on A1 choose all of my columns over to J from this point CTRL+SHIFT+DOWN ARROW.
In case Fred wants to add items later I’m going to add another i don't know 20 or so rows and let's see we have to make sure that that is locked down with the dollar sign so I'll press F4 right there make sure that we get four dollar signs.
If you would have typed this instead of selecting with the mouse or the arrow keys it tends to only put the dollar signs in the last part.
All right, so there we go, there's 120 volts once we have that formula we can just copy the formula that's why use those dollar signs.
I don't have to retype the formula on each one and now select a different item from the list and everything updates.
All right, now column B is ugly we have these helper cells that we don't want to see Fred had a copyrighted picture here.
If Fred drags that over on top of column B and extends it a little bit no one will see those answers in column B that's solution number one.
Solution number two, is to take everything at B cut it and move it over to column Z where no one will see it.
Solution number three, is just hide column B so right click and choose hide and no one will see those your picture here with the diagram you choose from the list and everything will update.
Alright, so, this was tougher because we had to use the helper function, the match function here the index function, those are confusing functions those are functions that people don't run into a lot.
But, they certainly make it easier to go grab the selected record from the other sheet and bring all of those answers back.
Hey, I want to thank Fred for sending that question and I want to thank you for stopping by, see you next time for another netcast of MrExcel.
All right, hey, if this was a Dueling podcast, this is really what I would do.
I would get rid of the plank road too.
I would make sure that all of the headings here matched the values back on sheet 1.
I would select this whole range and click up here in the name box and then that data and then finally CTRL+T so that way as I add new records to the bottom the name data automatically updates then back here on sheet1.
The formula says we're going to take the index of data the named range, which row 21 we want the match of A2 in column A comma 0 that's kind of like we did in the podcast and then which column we want the match of the value just above us in row 1 that will tell us where the word falls and the column copy that formula down.
Now we don't have anything in column B and a much more robust solution because as we add new data to the bottom of sheet2, it will automatically be incorporated in all these formulas.
We don't have to worry about hiding in column B everything just lives.
Yes, we are doing 7 or 8 matches instead of 1 match here.
But, it's only 7 or 8 it's not like we're doing 10,000 of these and it's all right into a table of 56 rows or 55 rows.
So, the performance hit isn't going to be that bad.
Learn Excel from MrExcel podcast episode 1803: Grab the Selected Record.
Hey, welcome back to the MrExcel netcast.
I’m Bill Jelen.
Today's question sent by Fred.
Can you help me with a formula the spreadsheet.
I created a drop-down a model number and I in A2, from sheet 2 and sure enough, yes data validation, set up there that's grabbing the model numbers from sheet 2.
So, we can choose from the list.
All right, and then what I'm trying to do is want a select a model.
Then put that information from the table on sheet2 into these various cells and what he's done is that =IF (A2=Sheet2!
A3, Sheet2!
C3) the first record the first model, then grabs the value from sheet2 C3 and this is all working great if we happen to choose the first model in the list.
But, if we don't choose the first model if we choose the second model in other words=IF(A2=A3) on sheet2 then we're getting all false is here and Fred realized that he's started down a path that would get really, really unwieldy very quickly.
He would have to do something like here in the value of false do another F statement IF (A2=Sheet2!
A4, Sheet2!
C4 and soon you'd have to do this putting like 55 different IF statements together which of course doesn't work.
Yeah, more than 32 it's just it's an unwieldy way to go.
All right, so let's use a couple of functions that will help with this the first thing I want to know is, I want to know which row did we select?
So, we selected item what row is that on and to do that we're gonna use =MATCH the match function will look for a value the selected value comma in this column and I'm going to choose the whole column here we don't have to choose the goal column I am and comma and then 0 because we want an exact match press ENTER and it says hey you chose an item from Row 3 let's do something else you chose an item from row 6 you chose an item from row 11 alright and so that is going to help in the formulas that follow.
All right, I'm going to use the same trick here to figure out which column we want to pull the data in.
So, these are various columns and so here I'm going to use match again =MATCH(A4, of voltage comma in Row one.
This time I'm going to press the F4 key here because I'm going to be copying this form of the down the sheet and I want to make sure I always want to row one that puts the dollar signs in see $1 $1,0 and what's up with that voltage.
All right, so we see that voltage heading there.
I see the voltage heading here for some reason is not singing match.
I'm going to press F2 and there's no space after the VOLTAGE you can see the flashing insertion point and come back to sheet1 and press F2 there's a space.
Okay, so, let's get rid of that space that allows me to match function to work all right we'll copy it down here.
All right, what's up here is our space no space.
Okay, so, here we have a Dash here we have a Slash.
All right, so, to make life easier friend these headings here should match the headings on sheet2 but they don't and that's okay, they don't have to there's it's not like we have a million of these we can just kind of go through here and see that hey, we're getting this from C3, C is the third column so i'll put a 3 there.
Here we're getting the value from D3 that's the 4th column, I'll put a 4 here.
Then E3, I'll put a 5 because E is the 5th column, 6th column, G is the 7th column, H is the 8th column and then J the 10th column.
So, I don't know what happened to whatever was in column 9.
But, for whatever reason this is what Fred wants to pull back so we now know the row number up here in the column number.
Now that we know that great little function called index =INDEX(answers,$B$2,B5) and we're going to give it an array of answers and come back for a second and then specify what row number alright the road numbers up here in B2.
I always want to grab the row number from being too, so I'm going to press F4 there to put the dollar sign be $2 comma and then which column number that's the number directly to the right of me closing parenthesis all right answers boy we could use a named range and just leave answers there but I'm not going to complicate things right now.
Therefore, the array I'm going to click back to sheet 2.
I'm going to start up here on A1 choose all of my columns over to J from this point CTRL+SHIFT+DOWN ARROW.
In case Fred wants to add items later I’m going to add another i don't know 20 or so rows and let's see we have to make sure that that is locked down with the dollar sign so I'll press F4 right there make sure that we get four dollar signs.
If you would have typed this instead of selecting with the mouse or the arrow keys it tends to only put the dollar signs in the last part.
All right, so there we go, there's 120 volts once we have that formula we can just copy the formula that's why use those dollar signs.
I don't have to retype the formula on each one and now select a different item from the list and everything updates.
All right, now column B is ugly we have these helper cells that we don't want to see Fred had a copyrighted picture here.
If Fred drags that over on top of column B and extends it a little bit no one will see those answers in column B that's solution number one.
Solution number two, is to take everything at B cut it and move it over to column Z where no one will see it.
Solution number three, is just hide column B so right click and choose hide and no one will see those your picture here with the diagram you choose from the list and everything will update.
Alright, so, this was tougher because we had to use the helper function, the match function here the index function, those are confusing functions those are functions that people don't run into a lot.
But, they certainly make it easier to go grab the selected record from the other sheet and bring all of those answers back.
Hey, I want to thank Fred for sending that question and I want to thank you for stopping by, see you next time for another netcast of MrExcel.
All right, hey, if this was a Dueling podcast, this is really what I would do.
I would get rid of the plank road too.
I would make sure that all of the headings here matched the values back on sheet 1.
I would select this whole range and click up here in the name box and then that data and then finally CTRL+T so that way as I add new records to the bottom the name data automatically updates then back here on sheet1.
The formula says we're going to take the index of data the named range, which row 21 we want the match of A2 in column A comma 0 that's kind of like we did in the podcast and then which column we want the match of the value just above us in row 1 that will tell us where the word falls and the column copy that formula down.
Now we don't have anything in column B and a much more robust solution because as we add new data to the bottom of sheet2, it will automatically be incorporated in all these formulas.
We don't have to worry about hiding in column B everything just lives.
Yes, we are doing 7 or 8 matches instead of 1 match here.
But, it's only 7 or 8 it's not like we're doing 10,000 of these and it's all right into a table of 56 rows or 55 rows.
So, the performance hit isn't going to be that bad.
