Data Validation Sort Order without sorting source data

[Will85]

Sheet 1 Column A = Customer ID
Sheet 1 Column B = Customer Name

Data in Column A and B is sorted Alphabetically by column B everytime a new record is created.

Sheet 1 Column C = Primary Customer ID

Each cell in Column C is a data validation list of Sheet 1 Column A, most records in Column C will be blank (no selection made in the drop down), but sometimes we have two customers under different names and numbers, that I want to sum their outstanding balances as a total.

Sheet 1 Column D = Outstanding Balance

Since my data is sorted alphabetically by name in column B, the sort order of the ID in column A is non sensical, selecting it in the drop down is difficult because column A is not sorted, So you have to hunt through the data validation drop down to find the number.

I wish their was a sort order option in the data validation window or something like that. I was thinking of using a combo box, but I would have hundreds of combo boxes.

 

[Akuini]

I was thinking of using a combo box, but I would have hundreds of combo boxes.

You only need a single combobox. Here's an example of a searchable drop-down, it gives you a unique, sorted & non-empty list, and you can search by multiple keywords:

Macro to create searchable data validation+combobox

Excel doesn't provide a built-in way to search data validation list. So it could be hard to scroll through the list when the list is long. This searchable data validation+combobox is a way to deal with that problem. It has these behavior: 1. The...

www.mrexcel.com

OR
use a free add-in called "Search deList"

I created an Excel add-in called “Search deList”, to create searchable data validation

I created an add-in called “Search deList”, and I share it as a freeware. Its function is to speed up searching in data validation list. In any cell that has data validation (with List type) pressing ALT+RIGHT will open a Userform with a combobox. You can type multiple keywords (separated by a...

www.mrexcel.com