I have a range that I want to make data validation list out of. The range has a lot of blank cell in it. I want the data validation list to only include the cell that have a value in them. The cells have formulas that go off another sheet, so I can't just delete the empty cells. I have attached a workbook if you want to see.
Not an easy solution for a validation list. See this article.
David
(*) Reputation points appreciated.
Pl see the file.
You would have to extract the data to a new range excluding the empty cells then use this new range as the source for the drop down list.
What version of Excel does this have to work in? The sample file is in *.xlsx format but your user profile says you're using Excel 2003.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If the list (with blanks) is in A1:A100, you can put the formula
=INDEX(A:A, SMALL(IF($A$1:$A$100="",999,ROW($A$1:$A$100)), ROWS($1:1)),1)&""
in B1. This should be entered as an array formula (Ctrl-Shift-Enter). The drag it down to B100. That should bring all the non-blank cells to the top and a validation list range of
=$B$1:INDEX($B:$B, COUNTIF($B:$B, "> "), 1)
will work for you. (the condition for the COUNTIF is "[greater than][space]")
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks