Data Validation List Length

[Guest]

For a data validation list, the default display length appears to be 8 lines.
If I am using a dynamic data range that is longer than 8, the dropdown list
displays the values at the end of the range. I have read various articles on
size and display of a dropdown list but have not found any reference to
this. Is there a way to format the data validation so that the drop down
list starts at the top of the range, or do I need to use a static range?
Thank you.

 

[Biff]

Hi!

What does the formula look like that defines the range?

Biff

 

[Guest]

I am using the following format:

=OFFSET(DATA!$B$2,0,0,COUNTA(DATA!$B:$B),1)

Thank you.

 

[Biff]

Hi!

Nothing obviously wrong with the formula.

That was the only thing I could think of that might cause the problem. I
have no other ideas, sorry!

Biff

 

[Debra Dalgleish]

When the Data Validation dropdown is opened, it tries to find an item in
the list that matches the current content of the cell.

Maybe your dynamic range includes a blank cell at the end, and the cell
with the Data Validation list is empty. If that's the case, you may need
to adjust the formula for your dynamic range, perhaps subtracting 1 for
the contents of a heading cell.