How to create an interactive invoice in Excel using Data Validation and VLOOKUP

Goal?

To automatically populate information in a document, e.g. an address field in an invoice template, by simply selecting a value from a drop-down list.

How?

1. Create a drop-down list in a cell, forcing the user to pick an item from this list (account number A1, A2, A3, etc. in the example above)
2. Use vlookup formulas to ensure the correct address is displayed, depending on which account number is selected.

Use the Excel function Data Validation to  create an in-cell drop-down list. This is a quick and easy way to control what can be entered in a cell and thereby avoid input errors. It can also be used for customers to choose yes or no, choose a date, etc. or for the user to select an option when you build an Excel dashboard.

Let’s go ahead and create an interactive invoice like in the image above. The user will select an account number from a dropdown and Excel automatically populates the name and address field using some basic vlookup formulas.

Download the workbook and test it out yourself! Link: How to create a dropdown list in Excel

How to create the drop-down list

1. 1. Start by creating the list of entries for the drop-down (A1, A2, etc.) somewhere within the workbook. The list should be in a single column (or row) and shouldn’t have any blank cells. It’s also a good idea to keep your validation lists on a separate worksheet.
   2. Giving the range a name makes things easier later on. This will also make it possible to add items to the range at a later stage, without having to go back and update all the cells containing the drop-down lists. They will automatically point to the updated list.
      The easiest way to do this is: Select the list of drop-down options, type a name in the Name Box and press Enter, as follows:
   3. Go back to your document and select the cell that you want to contain the drop-down list. Now select the function Data Validation, found in the DATA tab on the ribbon:
   4. In the Data Validation dialog box, in the Settings tab:
   5. 1. 1. id not name the range, then click in the Source field and select the range containing the list of entries to be used as your drop-down list.
            Tick “in-cell dropdown” and select “ignore blank” if it is fine for the user to leave the cell blank.

      That’s our drop-down done! Below are a few options to fine-tune your drop-down list’s behaviour:

      1. Select the Input Message tab
         If you enter a message here it will show up in a little box when the cell is selected. If you don’t want this, untick the checkbox:
      2. In the Error Alert tab:
      3. 1. Tick the check box “Show error alert after invalid data is entered” if you want an alert to pop up if an invalid response is entered.
            The default Style is “Stop”. This prevents users from entering an invalid entry.
            If you change the Style to “Warning” or “Information” the user can still enter whatever they want, but they get a message telling them that their entry is invalid (or whatever message you’ve typed into the “Error message” box.
            The difference between the two is the icon shown in the box:
            – “Warning”:  
            – “Information”:  

         Automating the name and address section

         1. This is done with a vlookup formula. The vlookup syntax is: Vlookup(what to look for, where to look for it, what column to bring back, FALSE). You add the FALSE to make sure it doesn’t just bring back the closest match if it can’t find the exact value you’re looking for.
            So the following formulas will bring back columns 2,3 and 4 respectively in the range of data:
            =VLOOKUP($E$17,validation!A:D,2,FALSE)
            =VLOOKUP($E$17,validation!A:D,3,FALSE)
            =VLOOKUP($E$17,validation!A:D,4,FALSE)
         2. Done! We have created the basics of an interactive invoice:
         3. Remember to download the Excel workbook and test it out for yourself. Also, please leave questions and comments below!