Data Validation List with only non-blank cells

I have a list in one worksheet which comes from "=SALESMEN!$D:$D" but the list is extremely long with blank values. How can I make the list only show values from column D which are non-blank? Thanks in advance.

Edit: Currently the list goes up to 30, however I want to use all of Column D from the SALESMEN worksheet, that way if I add to it, the names will automatically be added to the list in the other sheet. Thanks again

Use a dynamic named range
http://www.contextures.com/xlNames01.html#Dynamic

Originally Posted by Andy Pope

Use a dynamic named range
http://www.contextures.com/xlNames01.html#Dynamic

I've tried that. I went down and selected D1 through D100, and named it SALESMEN1. Then I put this in the source for the list

=OFFSET(SALESMEN1,0,0,COUNTA(SALESMEN1),1)

But I'm still getting all of the blanks at the end.

---

Also did this: Used a dynamic named range for SALESMEN2 which refers to =OFFSET(SALESMEN!$D$1,0,0,COUNTA(SALESMEN!$D:$D),1)
Then made the source of the list SALESMEN2, but still getting the same result with all the blanks.

Are you using the defined name SALESMAN1 in your formula to define the name SALESMAN1?
Define Saleman1 as

=OFFSET(D1,0,0,COUNTA($D$1:$D$500),1)

replacing the 500 with whereever you expect to max out. Does that work for you?

Are the cells truly empty

This works for me

It's still not working. Attached is the file I'm using...

On the LEADS sheet, the drop-down list is for the salesman column.

Try this

=OFFSET(Sheet1!$D$1,0,0,COUNTIF($D$1:$D$500,">"""),1)

Does that work for you?

It looks like your issue was resolved, so I hope you don't mind if I hyjack your thread since my issue is similar to yours.

I am using the equation mentioned, and I also have a long list of "blank" cells (they actually have equations in them that will fill in the cell when applicable). My new dropdown validation list recognizes the 2 cells that are not blank, however it shows 2 blank spots, instead of two actual items from my list. Did I make a mistake somewhere?

Hi sabbott13,

welcome to the forum. However,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.