CODEWITHSUNDEEP
excel
papi1992
papi1992

Reputation: 168

Can I add formula to in Data Validation, Allow, List?

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

Upvotes: 2

Views: 462

Answers (1)

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

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.

Upvotes: 2

Related Questions