Hello All,

I've looked all over this forum and tried to find the answer but I keep ending up with the same issue. Blank cell values. The only thing I've noticed on most of my searches is that most of these forums actually show data used in older versions of excel, so let me point out that I'm using Excel 2010 (version 14 if that matters).

What I've learned so far:
* Dynamic Range Names
* Using OFFSET and COUNTA (although still a little weary on it)
* Setting up Data Validation in list form

Here's what I'm doing (all within a single workbook using several worksheets):
* On the first sheet, I've arranged a column for people to add data ("Categories") too (range is from A5 to A100 - Sheet1)
* Sometimes people will skip rows (for whatever odd reason). They will also not populate the full 95 entries either, so the list will vary and generally only be half filled at most
* On the second sheet, they will add other data ("items") and then choose the "Category" from a dropdown that they originally added on the first sheet (range of items is Sheet2 - A5 to A200 and then B5 to B200 will show the dropdown to choose the category from Sheet1).

So far:
* If I just create a simple validation list, any skipped cells from Sheet1!A5:A100 will populate with the submitted data, even if I check off 'ignore blank' and will add the blank cells at the end of the list as well.

* If I use the formulas provided, I get rid of empty cells in the middle, but then still end up with empty values at the bottom of the list. Oddly enough when this happens, when I go to select the drop down, the list starts in the middle, leaving end-users thinking there's nothing there.

What I'm trying to accomplish:
* Let user add data to Sheet1!A5:A100, then be able to choose that in a list on Sheet2
* Validation list should not have any empty values (empty, #NUM, #REF or #VALUE entries listed in place of empty values either) nor should there be empty values at the bottom of the list
* When user clicks drop down, the list should start from the top

Please help. At this point I'm a little confused and tried quite a few things, so I'm not even sure what to refer to at this point (getting forum lost too lol).


Thanks