I know this thread has been written about a thousand times already, but I am literally going insane. I've spent days upon days searching the web, and can't help. I'm not a complete novice, but have only used basic functionality extensively, and I have never really used VBA, so hoping for a more simple solution. Thank you so much ahead of time if you have insight, I can't express my appreciation:

-I need to create a drop down list with cells that are non-contiguous. Under "List" in data validation, it will not let you choose non-contiguous cells, and if you choose contiguous cells with blank cells included, the blank spots show up in the drop down list. I need to figure out how to NOT have the blank cells appear in the drop down list (they are in the same column, just not contiguous rows).

Ex: I have 2 drop down lists 1) A1, A3, A5, A7 - with the list appearing in A9 and 2) B1, B3, B5, B7 - with the list appearing in B9. And obviously, even with the "Ignore Blank Cells" box checked in Data Validation box, they still appear. Seems like such a simple function, can't believe that it's this difficult to create.

-On top of that, I need to have an additional drop down list that is dependent on the 2 previous drop down lists. Ex: C3 will be a drop down list with the contents of A9 & B9 as the options.

-I have tried using dynamic named ranges, and cannot get it to work that way either (still won't accept non-contiguous cells in "List" function).

-I have also tried the idea of referencing another sheet (having the values of those cells show up in contiguous cells on that sheet). However, they problem I'm running into as that if "option 1" is selected in A9, and C3 - but then it is changed in A9, then C3 does not automatically change or go blank, it stays at "option 1".

So I guess my 2 questions are:

1). Is there a simple function to ACTUALLY ignore the blank cells in the drop down list? Some formula to use in dynamic named range "Name Manager" and then reference that thru "List" function possibly??

2). And if not, is there a simple formula for a 'validation order' so the cell with the 3rd drop down menu (dependent of 1 & 2) will return to blank if the inital option is changed?

Sorry to be so long-winded, I hope that's an accurate representation of my problem. It seems so easy, I can't believe I put so much time in and have absolutely no answers. Thank you very very very much for your time, I sincerely appreciate it-