Enter a product on Sheet 1. You want a VLOOKUP formula to grab the description from a lookup table on Sheet 2. However, if the product is missing from Sheet 2, you want Excel to stop, ask for the correct description, and then add that to the Lookup table so future entries for this product will get the proper description.
Today's episode uses a VBA Macro to achieve this result. The video is based on the question originally posted here: How to Create a dynamic Unique list that updates automatically
You can copy the VBA code from that link so you do not have to type the code.
Today's episode uses a VBA Macro to achieve this result. The video is based on the question originally posted here: How to Create a dynamic Unique list that updates automatically
You can copy the VBA code from that link so you do not have to type the code.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from Mr Excel podcast, episode 1868.
If item Is Missing from the Lookup Table, Add it Automatically.
Hey, welcome back to the Mrexcel netcast.
I'm Bill Jelen.
Well, today's question actually started out of the MrExcel message board a post here by earthworm and I figured this be a great one for the podcast.
So, let me explain the situation.
So, we have sheet one where we're doing data entry and our product number and the description is going to come from a lookup table on sheet 2.
All right simple enough, but earthworm is looking for once we enter that product in column A the description automatically appears in column B, but if the item is missing, if the item's not on the table, then we stop we ask the person doing the data entry for the correct description and add that description on the fly to sheet 2 is that way things continue to work.
All right! so now, the way I see if this is going to have to use VBA.
So, some pre-work that we have to do first, this is currently saved as xlsx.
You're not allowed to have VBA in xlsx.
Unfortunately, this is the default file type it's really insane that Microsoft use that as the default file type.
It's the only file type that does not allow VBA.
So, we're going to save as and change that to xlsm, xlsb, xls, I don't care anything but that horrible xlsx, save.
If you've never done macros before, do Alt+T+M+S and make sure that you are not set on that top one you want to go to the second choice.
All right and then the other bit of pre-work is we're gonna come back here to our VLOOKUP table and we're going to add a brand new item down here just ZZZ, Enter New Items Above This Row.
All right, good.
We need to switch over to VBA.
So that's Alt F11, this macro is not going to just be insert module we're gonna have to put it on the worksheet code pain for sheet 1.
If you're not seeing the Project Explorer.
Do View, Project Explorer or control+R and you might have to find your workbook in the list, click the plus sign next to your project, click the plus sign next to Microsoft Excel objects and then double click sheet 1 that'll get you to the worksheet code pain for that worksheet top left drop down choose worksheet and automatically they choose to go to selection change, but we're going to change that from the top right drop down choose change.
All right! So, there's the code that we're going to use today.
Now, there's a lot to type here, rather than type just go out to the MrExcel message board.
I'll put the link it to this question right there in the YouTube comments right down there and you can come down to the code that I've already typed here.
Select everything before the end sub to just before the sub, control+C to copy and come back here and paste that code in.
Boy, isn't that a lot easier than typing.
All right, a quick explanation of what is happening here.
Every time that they change something on sheet 1 this code is going to run they're going to give us an object variable called target, that is what just changed.
Now, sometimes you might go to sheet 1 and clear a bunch of cells.
So, if they changed a lot of cells, then the code is not going to run.
If they changed outside something outside of column 1, then the code is not going to run, but if they did change something in column 1, we're going to figure out what row they just changed.
So, we look at target.row figure out how many rows are currently on sheet 2 and then write the formula to the current row column 2 of sheet 1 and it's going to be a formula that does a VLOOKUP this is an R1C1 style, see that I've written the final row in there how many rows we have, we're looking for column 2, comma 2, comma false, it's like a typical VLOOKUP, I've wrap that in the IFERROR function and if it's not found that I put in the words not found.
All right! Now, one gotcha, there's a gotcha here before I'm allowed to write back to sheet 1, I have to turn off the enable events because if I didn't do that, if the macro wrote to column B, then this code would get called again.
All right! so, you always want to turn that off inside of a worksheet change, if you're about to write back to the worksheet.
All right! So, we put a VLOOKUP in check and see what answer we got there.
If it comes up to be not found, that we're going to flash an input box enter the description for this item you just entered and put a little title there's as you entered a new product.
Now, the problem that we have is some people are going to see that input blocks and they're just going to put cancel where they're just going to click OK, I and we're not going to have a description.
So, I have decided personally here to be a jerk about this.
If they click cancel, then I'm gonna assume that didn't mean to type that product and I am going to clear it out.
So, I go back up to the row they just entered I clear out that row and VLOOKUP that the Macro entered and then tell them hey, please re-enter the correct item number.
In real life I would be a much more sarcastic about this, I would say it looks like you click cancel, in my input box, I am assuming this means you typed the wrong item number in column A.
So, I have deleted it for you please re-enter.
You can make it however, pleasant or unpleasant you want it to be and then once we have the item so, they assuming they haven't entered a blank description go to sheet 2 to an entire row insert and then in that new row type the new item number they just typed and my description and then finally, turn back on the event handler.
All right! So, you know 38 lines of code.
You don't have to understand this code you can just copy and paste it from the MrExcel message board.
Let's give it a try first thing we want to do is enter product that is valid.
So, A107 come here, A107, I press ENTER and the VLOOKUP gets entered automatically for me and there is my value, but then if I come along, and and enter something that is not there J111.
You see the VLOOKUP is returning not found, the input box here says you entered new product and we can say that this is Jacima, press ENTER and two things have happened back on sheet 2 they added a new item above the ZZZ row with the new item and the description that i typed.
So, now in the future when i type that same item it will just automatically work it's in my table.
So, as I go most of the time the description is coming from the table, but every once in a while when I enter a new item, then it asked me to add it to the table.
If I would accidentally, miss type something so, A101 and I just shoot I press enter it deletes it and gives me my snarky message here.
So, I can re-enter.
So, there you go, I thought this was a real interesting question.
Certainly, a problem that people would have over and over, over, over.
So, decided to make a video here, but again you don't have to type that code.
just go out to the MrExcel message board and again the link to this topic is down there in the description at YouTube copy and paste.
Oh! hey, thank earthworm for sending that question in.
I Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from Mr Excel podcast, episode 1868.
If item Is Missing from the Lookup Table, Add it Automatically.
Hey, welcome back to the Mrexcel netcast.
I'm Bill Jelen.
Well, today's question actually started out of the MrExcel message board a post here by earthworm and I figured this be a great one for the podcast.
So, let me explain the situation.
So, we have sheet one where we're doing data entry and our product number and the description is going to come from a lookup table on sheet 2.
All right simple enough, but earthworm is looking for once we enter that product in column A the description automatically appears in column B, but if the item is missing, if the item's not on the table, then we stop we ask the person doing the data entry for the correct description and add that description on the fly to sheet 2 is that way things continue to work.
All right! so now, the way I see if this is going to have to use VBA.
So, some pre-work that we have to do first, this is currently saved as xlsx.
You're not allowed to have VBA in xlsx.
Unfortunately, this is the default file type it's really insane that Microsoft use that as the default file type.
It's the only file type that does not allow VBA.
So, we're going to save as and change that to xlsm, xlsb, xls, I don't care anything but that horrible xlsx, save.
If you've never done macros before, do Alt+T+M+S and make sure that you are not set on that top one you want to go to the second choice.
All right and then the other bit of pre-work is we're gonna come back here to our VLOOKUP table and we're going to add a brand new item down here just ZZZ, Enter New Items Above This Row.
All right, good.
We need to switch over to VBA.
So that's Alt F11, this macro is not going to just be insert module we're gonna have to put it on the worksheet code pain for sheet 1.
If you're not seeing the Project Explorer.
Do View, Project Explorer or control+R and you might have to find your workbook in the list, click the plus sign next to your project, click the plus sign next to Microsoft Excel objects and then double click sheet 1 that'll get you to the worksheet code pain for that worksheet top left drop down choose worksheet and automatically they choose to go to selection change, but we're going to change that from the top right drop down choose change.
All right! So, there's the code that we're going to use today.
Now, there's a lot to type here, rather than type just go out to the MrExcel message board.
I'll put the link it to this question right there in the YouTube comments right down there and you can come down to the code that I've already typed here.
Select everything before the end sub to just before the sub, control+C to copy and come back here and paste that code in.
Boy, isn't that a lot easier than typing.
All right, a quick explanation of what is happening here.
Every time that they change something on sheet 1 this code is going to run they're going to give us an object variable called target, that is what just changed.
Now, sometimes you might go to sheet 1 and clear a bunch of cells.
So, if they changed a lot of cells, then the code is not going to run.
If they changed outside something outside of column 1, then the code is not going to run, but if they did change something in column 1, we're going to figure out what row they just changed.
So, we look at target.row figure out how many rows are currently on sheet 2 and then write the formula to the current row column 2 of sheet 1 and it's going to be a formula that does a VLOOKUP this is an R1C1 style, see that I've written the final row in there how many rows we have, we're looking for column 2, comma 2, comma false, it's like a typical VLOOKUP, I've wrap that in the IFERROR function and if it's not found that I put in the words not found.
All right! Now, one gotcha, there's a gotcha here before I'm allowed to write back to sheet 1, I have to turn off the enable events because if I didn't do that, if the macro wrote to column B, then this code would get called again.
All right! so, you always want to turn that off inside of a worksheet change, if you're about to write back to the worksheet.
All right! So, we put a VLOOKUP in check and see what answer we got there.
If it comes up to be not found, that we're going to flash an input box enter the description for this item you just entered and put a little title there's as you entered a new product.
Now, the problem that we have is some people are going to see that input blocks and they're just going to put cancel where they're just going to click OK, I and we're not going to have a description.
So, I have decided personally here to be a jerk about this.
If they click cancel, then I'm gonna assume that didn't mean to type that product and I am going to clear it out.
So, I go back up to the row they just entered I clear out that row and VLOOKUP that the Macro entered and then tell them hey, please re-enter the correct item number.
In real life I would be a much more sarcastic about this, I would say it looks like you click cancel, in my input box, I am assuming this means you typed the wrong item number in column A.
So, I have deleted it for you please re-enter.
You can make it however, pleasant or unpleasant you want it to be and then once we have the item so, they assuming they haven't entered a blank description go to sheet 2 to an entire row insert and then in that new row type the new item number they just typed and my description and then finally, turn back on the event handler.
All right! So, you know 38 lines of code.
You don't have to understand this code you can just copy and paste it from the MrExcel message board.
Let's give it a try first thing we want to do is enter product that is valid.
So, A107 come here, A107, I press ENTER and the VLOOKUP gets entered automatically for me and there is my value, but then if I come along, and and enter something that is not there J111.
You see the VLOOKUP is returning not found, the input box here says you entered new product and we can say that this is Jacima, press ENTER and two things have happened back on sheet 2 they added a new item above the ZZZ row with the new item and the description that i typed.
So, now in the future when i type that same item it will just automatically work it's in my table.
So, as I go most of the time the description is coming from the table, but every once in a while when I enter a new item, then it asked me to add it to the table.
If I would accidentally, miss type something so, A101 and I just shoot I press enter it deletes it and gives me my snarky message here.
So, I can re-enter.
So, there you go, I thought this was a real interesting question.
Certainly, a problem that people would have over and over, over, over.
So, decided to make a video here, but again you don't have to type that code.
just go out to the MrExcel message board and again the link to this topic is down there in the description at YouTube copy and paste.
Oh! hey, thank earthworm for sending that question in.
I Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
