blanks in data validation (Drop down list)

[ajl_ahmed]

How can remove the blanks in data validation (Drop down list)? I have this error appeared as in the attached inage.

[#2 · Accepted solution]

The issue is you cannot include dynamic arrays in the data validation menu. You'd have to add the formula at the sheet level then refer back to the spill using # notation. If the blanks in your range are at the end, you might consider creating an old school dynamic range with OFFSET-COUNTA.