Forum Discussion
Solved
Dynamic named range + data validation not working as expected
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.
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.
- Hello Hans,
B1 is the column header and B2 is empty.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.
