Drop Down List

[southike]

My work has an excel sheet that we upload to our core system. The sheet has to be in a very specific format. One column is for "account number". I want to create a drop down for the user, but if the drop down merely shows the account number, that does not help them. They need to be able to see the description of the account number as well. For example: 222333444 means nothing to them since they don't have all account numbers memorized. But if the drop down shows "222333444 Insurance Fees", then they would know which account number to select. However, the cell/column can be ONLY a number. It can't include the wording or the upload will fail. Any help is appreciated. Maybe a drop down is not the answer. Thank you.

 

[Akuini]

See if this example (post #4) helps:
data-validation-list-quandary

 

[southike]

Yes, this does help. I've created the "helper" column. And I've added the VBA to my sheet, but I'm getting the error "Wrong Entry". I edited the ranges in the VBA code to match mine. The columns are actually the same, just added some more rows. Any reason why I'm getting this error?

Edit: I may have found the problem. It's looking for E2:Ex..., but my "E2:Ex" is on another sheet. How do I edit the VBA to look at my other sheet. The other sheet is called "TABLES".

 

[Akuini]

Edit: I may have found the problem. It's looking for E2:Ex..., but my "E2:Ex" is on another sheet. How do I edit the VBA to look at my other sheet. The other sheet is called "TABLES".

Try changing this part:

Set c = Range("E2:E11").Find(What:=v, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

to this:

Set c = Sheets("TABLES").Range("E2:E11").Find(What:=v, LookIn:=xlValues, lookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)

 

[southike]

That worked perfectly. Thanks so much!

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.
Is the range "E2:Ex" a fixed range, or is it dynamic , meaning you plan to add new data row later on? If it's dynamic, we'll need to modify the code accordingly.

 

[southike]

It is fixed at E2:E21. Shouldn't be changed. But if it ever does change, you're right I'll have to edit that. Thanks for the heads up.

 

[Akuini]

Ok, great.