SOLVED

Dynamic named range + data validation not working as expected

Copper Contributor

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:

 

hareempotter_0-1711026665191.png

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:

 

hareempotter_1-1711026776305.png

 

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.

6 Replies

@hareempotter 

 

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

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

@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.

@Hans Vogelaar 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.

best response confirmed by hareempotter (Copper Contributor)
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.

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

Accepted Solutions
best response confirmed by hareempotter (Copper Contributor)
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.

View solution in original post