Forum Discussion
Dynamic named range + data validation not working as expected
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.
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.
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.
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.