How to Auto-Fill Customer Addresses with an Invoice Drop-Down in Excel
Question details
The user wants to create a dynamic drop-down list in an invoice workbook that automatically populates the customer's name and address when selected, and updates automatically when new customers are added.

- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Creating an automated invoice template where selecting a customer from a drop-down list auto-fills their billing details.
- Observed behavior
- The user needs a solution combining data validation and lookup formulas linked to a dynamic table to achieve automated data entry.
Ensure you have a dedicated worksheet containing your customer database with columns for Customer Name, Address, City, and other relevant billing details.
Use Data Validation and VLOOKUP with an Excel Table
By formatting your customer list as a Table, the drop-down list will automatically expand when new customers are added. VLOOKUP formulas will then fetch the corresponding address.
Using an official Excel Table for your source data is the key to making your drop-down list dynamic. When you add a new customer to the bottom of a Table, the data validation range expands automatically without needing manual adjustments.
Go to your customer database sheet, select the entire range of your customer data (including headers), navigate to the 'Home' tab, and click 'Format as Table'. Choose a style and confirm that your table has headers.
Go to your Invoice sheet and select the cell where you want the customer name drop-down to appear. Click the 'Data' tab, select 'Data Validation', set the 'Allow' criteria to 'List', and select the customer name column from your newly created table as the source.
In the address cells below the drop-down, enter a VLOOKUP formula to pull the address based on the selected customer name. For example, use =VLOOKUP(B5, CustomersTable, 2, FALSE) where B5 is your drop-down cell, CustomersTable is your data table, and 2 is the column index for the address.

Automate Your Invoices Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful Data Validation and VLOOKUP functions that are fully compatible with Excel, making it incredibly easy to build automated, dynamic invoice templates.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your invoice template.
- 2. Set up Data Validation: Navigate to the Data tab, click 'Validation', and set your customer list as the source.
- 3. Insert Lookup Formulas: Use the built-in VLOOKUP function to automatically fetch and display the customer's address.

Frequently Asked Questions
Why isn't my drop-down list updating when I add new customers?
If your drop-down list doesn't update automatically, your source data might not be formatted as a Table. Select your customer data and press Ctrl+T (or go to Home > Format as Table) to convert it into a dynamic table.
Can I use XLOOKUP instead of VLOOKUP for the invoice address?
Yes, if you are using a newer version of Excel or WPS Office that supports XLOOKUP, you can use it to fetch the address. XLOOKUP is more flexible as it doesn't require the lookup value to be in the first column.
How do I hide the #N/A error when no customer is selected?
You can wrap your VLOOKUP formula in an IFERROR function. For example, use =IFERROR(VLOOKUP(...), "") to display a blank cell instead of an error when the drop-down is empty.




