Limited Data Validation Choices

[TheRogue]

[TABLE="class: grid, width: 250"]
<tbody>[TR]
[TD]THINGS
[/TD]
[/TR]
[TR]
[TD]Red
[/TD]
[/TR]
[TR]
[TD]Blue
[/TD]
[/TR]
[TR]
[TD]Yellow
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have the above table that I am using as a Data Validation List. The Data in the Table is dynamic & does not always have the same # of items in it. For the purpose of Data Validation, I have given Table1[THINGS] the Named Range of "LIST" - So, my Data Validation filter is =LIST. It works great!

The problem is that the Data Selection Box Starts at the bottom & includes all of the blanks from Table1[THINGS].
Is there a way to tell Data Validation to ignore the Blanks? I can't limit the range, b/c they might be populated, next time.

 

[offthelip]

You can do this by using counta and offset in the data validation. :
Assuming the validation is in column I, put this in the list source field:
=(OFFSET(I1,0,0,COUNTA(I1:I100)))

 

[DanteAmor]

Define Name: LIST
Refers to:

Put the following formula:

=INDIRECT("Sheet1!$A$2:$A$" & COUNTA(Sheet1!$A:$A))


Change Sheet1 by your Sheet, "A" by your column.