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

Watch on YouTube

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