List not showing all values from dynamic range

[Camel123]

Hi,

I have created a dynamic cell range which is working. If typing "a" in the drop down list, the dynamic range it refers to determines all values containing "a". Now the problem is the drop down list only displays the first four values containing "a" and there is no scroll available to see all other values in that range.

What is the issue?

 

[Camel123]

EDIT:
I believe it has something to do with the range being dynamic. When creating a drop down list without a dynamic range then the scrollbar appears.

Also, how can I display more than 4 values in the drop down list (up to 8 rows lets say)?

 

[Twollaston]

EDIT:
I believe it has something to do with the range being dynamic. When creating a drop down list without a dynamic range then the scrollbar appears.

Also, how can I display more than 4 values in the drop down list (up to 8 rows lets say)?

You can try the step by step guide here
https://www.extendoffice.com/documents/excel/4021-excel-data-validation-dynamic-range.html
there is also a lot of youtube videos on how to make these list, I would suggest following along with one while you are setting your specific ranges to match your data because it's going to be hard for someone to help you with this without knowing where all your data is located

Or if you can share the cell with the validation menu and where the menu is populating from

 

[Camel123]

Hi,

I followed this tutorial:
https://www.youtube.com/watch?v=vkPoViUhkxU

Instead of vlookup I used index/match since vlookup did not function as intended even though it was an exact copy of his formula. In the offset formula I used SUMPRODUCT and LEN instead of countif since I want to include values containing any characters. So still it did not work. Afterwards I deleted the range named "validation_list", created a new one called "dynamicrange" and used it as reference in the drop down list= it worked. Not really sure on what was causing the disabled scrollbar at the beginning, probably me that did something wrong in the first named range.

Now I am facing a new issue, the scrollbar is starting at the bottom of the list and not top, suggestions on how to solve this?

 

[Camel123]

I added a space to the dynamic range if no false, instead of "", now the scrollbar starts from the top. Though not sure why so if anyone could explain I'd appreciate it.