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.

 

[southike]

This has been working flawlessly. But now, our team members occasionally need to type in an account number that is not one of the "normal" ones they use. It's the customers account number and we have thousands of customers, so I can't really put that in the drop-down. I need the first two cells (A2 and A3) to allow any number to be typed in...not from the drop down. I've removed the data validation for those two cells, but when I type in a random account number I'm getting the error message "Wrong Entry". Can this be done somehow? Thank you for your help.

 

[Akuini]

I need the first two cells (A2 and A3) to allow any number to be typed in

Try changing this part:

                   If Not c Is Nothing Then
                        Target = v
                    Else
                        MsgBox "Wrong Entry"
                        Target.ClearContents
                    End If

to this:

                    If Not c Is Nothing Then
                        Target = v
                    Else
                        If Intersect(Target, Range("A2:A3")) Is Nothing Then
                            MsgBox "Wrong Entry"
                            Target.ClearContents
                        End If
                    End If