How to exclude empty cells from a list?

I have an Excel 2007 version. I have checked the "ignore blank" box, but nothing changes; my list contains blanks and I don't want these blanks.

After extensive research, I think I finally found a solution provided here with an example: http://forum.excel-pratique.com/excel/liste-deroulante-avec-ignorer-si-vide-t3936.html

The person has the same problem as me; the "ignore blank" box makes no difference whether checked or not.

However, if someone has a simpler solution, I'm all ears, because it seems quite complicated for a function that seems relatively simple.

No, I can't sort, my database is dynamic, so I have an imperative need to be able to create a dropdown list based on a column where the blanks are likely to change. Is there no simple way to remove empty cells? I'm really struggling here, even with the formula I found that doesn't work properly for my application.

I'm answering myself; I found the solution for the discrepancy in this file:

http://boisgontierjacques.free.fr/fichiers/DonneesValidation/ListeCond.xls

It's even better than the previous method and easier to implement.

My procedure:
* Select A2:A13
* Copy
* Paste Special/Values in E2
* Sort from A to Z
* Define the name "List" for the range E2:E13 (or E2:E7) and use it as the source for validation: https://www.cjoint.com/c/CAhnsBodrL4

Best regards.

Retirement is great! Especially in the West Indies ... :-)
☻ Raymond ♂

I don't understand your question!!

When you create a list, if there are empty cells, they are not included in your list.. unless you are using a very old version of Excel!!

To create a list, press "CTRL+Shift+L" simultaneously, select the cells that will be part of this list, then press "Enter"

You need to copy the list to another location, then sort it before selecting the interesting range.

--
Retirement is great! Especially in the Caribbean... :-)
☻ Raymond ♂

Do you have an example of a formula that I can use without VBA?

I already have this formula =MIN(IF(NOT(ISBLANK(OFFSET($A1:$A$10,COUNTA($A$1:$A1),;;)));ROW(OFFSET($A1:$A$10,COUNTA($A$1:$A1),;;))))

This formula works very well to find the row number of the first non-empty cell as long as the empty cells do not come from a formula with a result of "", because unfortunately, cells resulting in "" are not considered empty. Is there a way to modify this formula to ignore something other than an empty cell, for example a 0 or text? Thank you.