2

I think for instance I select an A1:A1000 range, but just I want cells with data got it. So I have an 1000 cells range, but inside the range has 30 cells with data, and when I would like to select an value from my drop down list, I will see the 30 value without empty value.

I think for this one...

image1

and this one...

image2

Improve this question
2
  • Are the 30 cells with data out of the 1000 cells the first 30 cells? If so, then this is possible. Otherwise it is not possible without having a helper column since the List must be a successive range. Commented Jul 22, 2016 at 10:27
  • I use an helper Column, and I make an Range with AA3:AA1000, after I set up the validation, just now I have 1000 record with 970 empty values in my drop down list. Commented Jul 22, 2016 at 11:08

1 Answer 1

Reset to default
2

If the data is always at the top of the the range, with no blanks half way down, use the Name Manager and create a range using the following:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

If you set the Named Range to be titles for example, in data validation select List and put =titles in the source.

Of course change Sheet1! to the name of the sheet where your values are.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Great answer. Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.