Data Validation List with No Blanks

[zacheisjd]

I'm currently configuring a workbook that we send to customers to fill out with their employee names, emails, manager names etc. So I would have something like this:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Employee Name[/TD]
[TD]Manager[/TD]
[TD]Organization[/TD]
[/TR]
[TR]
[TD]Jane Smith[/TD]
[TD][/TD]
[TD]Org 1[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD]Jane Smith[/TD]
[TD]Org 2[/TD]
[/TR]
[TR]
[TD]No Data[/TD]
[TD]No Data[/TD]
[TD]No Data[/TD]
[/TR]
[TR]
[TD]No Data[/TD]
[TD]No Data[/TD]
[TD]No Data[/TD]
[/TR]
</tbody>[/TABLE]

The Manager column would have data validation list using A2:A1000 (I don't know how many names they will enter so I have to make the range large enough to accommodate a large list). The problem is that using data validation list doesn't ignore the blanks and populates them in the dropdowns making it unnecessarily long and difficult to use.

My goal is to find some way to get the data validation list but without the blanks populating from the large range. I cannot use VBA/macros because some customers don't allow their use in their company and/or users may not have it enabled.

Any direction is much appreciated.

 

[Roderick_E]

=OFFSET(functions!$J$4,0,0,COUNTIF(functions!$J$4:$J$65536,"> "))

This is what you'll put in the data validation field, change the functions!$J to the sheet name and range you need.

 

[zacheisjd]

That is perfect, thank you so much.