Searchable Dropdown 2

I made the drop-down in Sheet1 of the attached workbook work. I defined a named range 'ListRange' as $D$2:$D$1000, and a second named range 'ListFill' like this.

=ADDRESS(ROW(ListRange),4,1,1,"Sheet1")&":"&ADDRESS(ROW(ListRange)+ROWS(ListRange)-COUNTBLANK(ListRange)-1,4)

Note that the "4" in the formula defines column 4 (= D). 'ListFill' adjusts to include only the used part of 'ListRange' and is assigned to the 'ListFillRange' property of the combo box.

Note that you can't use the combo box's Change event to drop down the list. Since your Cbx has an arrow, clicking on that arrow will open the list. If you want to automate that you might use the WorkSheet_Activate event, perhaps, or some other worksheet event, and probably configure the Cbx without the drop-down arrow which, by the way, triggers the 'DropButtonClick' event.

The fanciful behaviour of your original combo box was caused by the presence of the named ranges 'idname'. After I removed them everything become normal. I didn't find out if there is anything wrong with the formulas you used to define those ranges. Instead, I presume that the problem was that you declared 4 ranges all of the same name. You can declare ranges of identical name on different worksheets. That will lead to occasional confusion. But you can't use the same name again on Workbook level, and you can't use any name twice within the same scope.