Dynamic named range + data validation not working as expected

[hareempotter]

Hello,

I have a use case where I need to set up a dynamic named range comprised of an entire column.

This is the formula I am using to define the range (found on YouTube):

=OFFSET('Document Tree'!$B$3,0,0 COUNTA('Document Tree'!$B:$B)-1,1)

Then on another sheet, I select the column I need data validation, Select List, Ignore Blanks and Dropdown in Cell. And I enter the name of the dynamic range as the source. The result is close to what I need, but something is breaking the dropdown options:

In the screenshot above, there are two options, PLN-1608 and a blank. The issue is, that blank should be another string value. When I add a third string value to the dynamic range, it shows as a second blank in the dropdown:

So what is going on? The dropdown is showing the options but the text isn't being displayed. How do I fix this?

Thank you in advance.

[#2]

@hareempotter 

What do B1 and B2 on the Document Tree sheet contain?

[#3]

Hello Hans,
B1 is the column header and B2 is empty.

[#4]

@hareempotter 

It should work.

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

[#5]

@HansVogelaar certainly.

I re-created the exact issue in the attached workbook. Two options in the range, one of them is showing up as a blank in the dropdown menu.

[#6 · Accepted solution]

@hareempotter 

That is because B4 on Sheet1 is empty. The source range of a data validation drop-down list should be a contiguous range without blanks.

In the attached version I have created a workaround using the FILTER function.

[#7]

This fixed the issue, thanks Hans! And appreciate the advice regarding data validation needing a contiguous range.