Excel Data Validation Drop-Down for Account Names and Numbers
Question details
The user needs a data-validation drop-down that shows both an account number and account name while storing only the account number in the selected cell, without VBA.
- Product
- Microsoft Excel data validation
- Device & OS
- not provided
- Scenario
- An account list contains numbers and names, and the user wants a user-friendly selection list with the number saved as the underlying value.
- Observed behavior
- Standard Excel data validation writes the selected list item directly into the cell, so it cannot display one value and store a different value in that same cell without VBA.
Place the account numbers and account names in separate source columns, such as columns A and B, and decide whether the selected cell or an adjacent helper cell will contain the stored number. Make sure the source range has no blank or duplicate entries that could make the list confusing.
Use combined account number and name entries, then extract the number
Use this no-VBA workaround when the drop-down must show both pieces of information and the selected cell can contain the combined text.
Excel data validation stores the exact text selected from the list. Create a helper column with entries such as “10000 - Cash Account,” use that column as the validation source, and extract the number in a neighboring cell with a formula.
In a helper column, enter a formula such as =A2&" - "&B2, where column A contains the account number and column B contains the account name. Fill the formula down for all accounts.
Select the destination cells, open the Data tab, choose Data Validation, select List under Allow, and set the Source to the helper range containing the combined entries.
In an adjacent cell, use a formula such as =VALUE(LEFT(C2,FIND(" - ",C2)-1)), replacing C2 with the cell containing the selected combined entry.
Store the account number and look up the account name beside it
Use this option when the selected cell must contain only the account number and the account name can be displayed in a neighboring cell.
Try WPS Spreadsheets for Flexible Account List Workflows
WPS Spreadsheets provides a familiar, lightweight spreadsheet interface for opening and editing Excel workbooks, including data-validation lists, helper formulas, and lookup-based account displays. It is a practical free alternative for migrating this workbook while preserving common Excel formats, although the same one-cell display-versus-storage limitation applies to standard validation lists.
- 1. Open the workbook in WPS: Launch WPS Office, choose Spreadsheet, and open the existing .xlsx or .xls workbook from File > Open.
- 2. Build the helper workflow: Use the Data > Data Validation menu to create the account list, then add a neighboring formula column for combined labels or account-name lookups.
- 3. Save in an Excel format: Choose File > Save As and select .xlsx or .xls when the workbook must be shared with Microsoft Excel users.
Frequently Asked Questions
Can Excel data validation show an account name but save only the account number in the same cell?
Not with standard data validation alone. The selected list item is stored as the cell value, so showing one value while storing another requires VBA or a separate helper and display arrangement.
How can I show both the account number and account name in the drop-down without VBA?
Create a helper column with entries such as 10000 - Cash Account, then select that helper range in Data > Data Validation > List. The chosen cell will contain the combined text.
How do I keep only the account number in the selected cell?
Use the account-number range as the Data Validation list source, then use XLOOKUP or VLOOKUP in an adjacent cell to display the matching account name.
What formula extracts the account number from a combined list entry?
If the selected text is in C2 and follows the format 10000 - Cash Account, use =VALUE(LEFT(C2,FIND(" - ",C2)-1)) in a neighboring cell.
