Function Problems

How to Create a Customer Address Drop-Down List for an Invoice in Spreadsheet

John WilsonJohn Wilson Nov 10, 2026 870 views

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.

How to Create a Customer Address Drop-Down List for an Invoice
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.
Before you start

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.

Solution 1Recommended

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.

1
Convert the Customer List to a Table

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.

2
Define a Name for the Customer Column

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.

3
Create the Drop-Down List on the Invoice

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.

4
Add the VLOOKUP Formula for the Address

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.

Use a Dynamic Table, Data Validation, and VLOOKUP
Dynamic Expansion Enabled: Whenever you type a new customer name and address at the bottom of the 'Customers' table, it will instantly become available in the Invoice drop-down menu without needing to adjust the formula.
WPS Office Direct Solution

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. 1. Open your Workbook: Launch WPS Spreadsheet and organize your data into 'Invoice' and 'Customers' sheets.
  2. 2. Format Data as a Table: Use the Insert > Table feature on your customer data to make the range dynamic.
  3. 3. Apply Data Validation: Select the invoice drop-down cell, go to Data > Validation, and create a List based on your customer names.
  4. 4. Insert Lookup Formulas: Use WPS Spreadsheet's built-in VLOOKUP or XLOOKUP to automatically pull the address when a customer is selected.
100% compatible with Microsoft Excel (.xlsx) formulas, including VLOOKUP and XLOOKUP.Intuitive Data Validation features to create dynamic drop-down lists in seconds.Free access to hundreds of ready-to-use professional invoice templates.Cross-platform support to manage your customer database on Windows, Mac, or mobile.
microsoft office alternative - wps office

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.