Forum Discussion

benji45645's avatar
benji45645
Copper Contributor
Dec 04, 2023

Data Validation List that displays one value and inputs another value

I'm making a journal entry worksheet that has easy reference to a chart of accounts on one of the other sheets. On the journal entry sheet, there is a dropdown list under Account Number. This dropdown list references the Chart of Accounts table on another worksheet. That table has a column for the account number and another for the account name. I can make a combined column that has

"AccountNo - AccountName"

Since autocomplete is now integrated into excel, you don't have to type the whole query value to get the result you want. I can type "10" to get the following entries to display:
10000
10100
10200

Is it possible to have the list show this:
10000 - Cash Account
10100 - Petty Cash
10200 - Undeposited Funds

While still making it so that when I choose "10000 - Cash Account", for example, it only fills the data validation cell with "10000" instead of "10000 - Cash Account"?

In short, I want the dropdown list to show what the account number means while only having the account number input into the final journal entry. I'd like to avoid VBA if possible to keep the workbook usable when sent to accountants via email.

Is this possible? Thanks in advance.

Resources