Populate dropdown list excluding nonblank cells | General Excel Questions & Answers | Excel Forum

Hi everyone,

Attached are 3 files: my files (forum_file and forum_file2) and reference file from another topic here (excel_dynamic).

A column has cells with formulas inside them but don't return any values. The reference file (excel_dynamic) is fantastic in populating the drop down list with only those that return a value (meaning, it excludes cells with formulas but don't return any values).

However, the file is limited to one column only.

I need the drop down list to return different columns, depending on the header name. This formula works great: =INDEX(Sheet1!$A$1:$C$11,0,MATCH($A$1,Sheet1!$A$1:$C$1,0)) BUT it returns the nonblank cells. 

Can someone help me? I've been stuck on this for weeks. I did find a solution that involves name ranges (as in the file: forum_file2), but I'm hoping not to do this since my actual data needs hundreds of drop down lists based on the value of another cell. If I do name ranges, I will end up creating name ranges (and their helper name ranges) for hundreds of drop down lists.

THANK YOU SO MUCH!!