Excel - Self-Maintaing Lookup Table - Episode 1868
3,978 views · Published 11 March 2014 · 8:14 · Indexed 16 November 2026
Channel: MrExcel.com · 2014 · Education
Microsoft Excel Tutorial: Creating a Self-Maintaining Lookup Table in Excel with VBA | MrExcel Podcast. Welcome to another episode of the MrExcel podcast. In this episode, we will be discussing a question from the MrExcel message board, posted by user earthworm. The question is about creating a self-maintaining lookup table in Excel. Specifically, how to automatically add a missing item to the lookup table and continue with the data entry process. The situation is as follows: on sheet one, we have a data entry table where the product number is entered in column A. The corresponding description is supposed to be retrieved from a lookup table on sheet two. However, if the product number is not found in the lookup table, the user is prompted to enter the correct description and the new item is added to the lookup table on the fly. This ensures that the data entry process can continue smoothly. To achieve this, we will need to use VBA. Before we begin, make sure to save the file as xlsm, xlsb, or xls, as xlsx does not allow VBA. Then, go to the second choice under Alt+T+M+S to enable macros. Additionally, we will need to add a new item to the lookup table on sheet two, as shown in the video. Next, we will switch over to VBA by pressing Alt+F11. We will need to insert the code on the worksheet code pane for sheet one. If you do not see the Project Explorer, go to View and select Project Explorer or press control+R. Then, double click on sheet one under Microsoft Excel objects. From the top left drop down, select worksheet and from the top right drop down, select change. The code for this episode can be found on the MrExcel message board, and the link is provided in the description below. The code will run every time a change is made on sheet one. It will check if the change was made in column one and if so, it will retrieve the corresponding description from the lookup table on sheet two. If the item is not found, an input box will appear asking for the correct description. If the user clicks cancel, the entered product number will be deleted and the user will be prompted to re-enter the correct item number. Once the correct description is entered, it will be added to the lookup table on sheet two and the data entry process can continue. In conclusion, this code allows for a self-maintaining lookup table in Excel, ensuring that the data entry process can continue smoothly even if a new item needs to be added to the lookup table. The code may seem lengthy, but it can be easily copied and pasted from the MrExcel message board. We hope this episode was helpful and we would like to thank earthworm for sending in this question. Thank you for watching and we'll see you next time for another netcast from MrExcel. Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ Table of Contents: (00:00) Automatically Adding Missing Items to a Lookup Table (00:25) Pre-Work: Saving as a Compatible File Type and Enabling Macros (01:02) Switching to VBA (02:00) Code Explanation and Copying from MrExcel Message Board (03:12) Flashing an Input Box for New Item Description (05:25) Adding New Item to Lookup Table (06:20) Testing the Code (07:27) Handling Mistyped Items (07:45) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Data entry Description Episode 1868 Input box Learn Excel Lookup table Sheet 1 Sheet 2 VBA VLOOKUP Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153693/ 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: http://www.mrexcel.com/forum/excel-questions/763124-how-create-dynamic-unique-list-updates-automatically.html You can copy the VBA code from that link so you do not have to type the code.
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920