How to Create a Customer Address Drop-Down List for an Invoice in Spreadsheet
Question details
The user wants to configure an invoice template where selecting a customer from a drop-down list automatically retrieves and displays their address, with the ability to dynamically add new customers to the list later.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up an automated invoice document that pulls client information from a separate database sheet.
- Observed behavior
- Needs to create a 'Bill To' drop-down selector that connects to an expandable customer list and uses lookup formulas to populate corresponding address fields.
Ensure your workbook contains two separate sheets: an 'Invoice' sheet where you will create the drop-down list, and a 'Customers' sheet containing a clean dataset with 'Customer Name' in the first column and 'Address' in the second column.
Use a Dynamic Table, Data Validation, and VLOOKUP
Converting your customer dataset into a dynamic table ensures that any newly added customers automatically appear in your invoice's drop-down list. VLOOKUP handles retrieving the corresponding address.
This method relies on creating a defined name for your dynamic table column, applying it to a Data Validation list, and utilizing a VLOOKUP formula to fetch the address based on the selected name.
Navigate to the 'Customers' sheet. Select all your existing customer data (including headers), go to the 'Insert' tab, and click 'Table' (or press Ctrl+T). This ensures the range expands automatically when new rows are added.
Select all the customer names in your newly created table (excluding the header). Go to the 'Formulas' tab, click 'Define Name', enter a name like 'CustomerList', and click OK.
Go to the 'Invoice' sheet and click the cell under 'Bill To' where you want the drop-down. Navigate to the 'Data' tab, click 'Validation', choose 'List' from the Allow drop-down, type '=CustomerList' in the Source box, and click OK.
Select the cell directly below your drop-down list where the address should appear. Enter the formula `=VLOOKUP(B10, Customers!A:B, 2, FALSE)` (replace B10 with your drop-down cell reference, and adjust A:B if your table spans different columns). Press Enter.

Automate Your Invoicing with WPS Spreadsheet
WPS Spreadsheet provides robust Data Validation tools and advanced lookup functions, allowing you to easily build dynamic, professional invoices that save you hours of manual data entry.
- 1. Open your Workbook: Launch WPS Spreadsheet and organize your data into 'Invoice' and 'Customers' sheets.
- 2. Format Data as a Table: Use the Insert > Table feature on your customer data to make the range dynamic.
- 3. Apply Data Validation: Select the invoice drop-down cell, go to Data > Validation, and create a List based on your customer names.
- 4. Insert Lookup Formulas: Use WPS Spreadsheet's built-in VLOOKUP or XLOOKUP to automatically pull the address when a customer is selected.

Frequently Asked Questions
Why isn't my drop-down list updating when I add new customers?
If your Data Validation source uses a static range (like Customers!$A$2:$A$10), it will not include new rows. Make sure you converted your customer data into a Table (Ctrl+T) and created a Defined Name for the column, which tells the drop-down to expand automatically.
How do I hide the #N/A error when no customer is selected in the invoice?
You can hide the error by wrapping your lookup formula inside an IFERROR function. Change your formula to `=IFERROR(VLOOKUP(B10, Customers!A:B, 2, FALSE), "")`. This will display a blank cell instead of an error when the drop-down is empty.
Can I use XLOOKUP instead of VLOOKUP for the customer address?
Yes. XLOOKUP is an excellent, flexible alternative because the customer name doesn't have to be in the first column. You can use a formula like `=XLOOKUP(B10, Customers!A:A, Customers!B:B, "")` to achieve the same result.




