Using Defined Name for lists -- two small issues that I cannot solve

So, I am using a Defined Name to generate dynamic drop-down lists in excel.

How I did this was to create a worksheet called 'Dynamic Lists' to form the lists as I intended, then in a Defined Name, I used the formula:

Please Login or Register  to view this content.

This generates a list based on the title of the column. There are other ways to generate dynamic dropdown lists, but you cannot use special characters, which is necessary in this case for me.

My issue is that the second part of the list:

Please Login or Register  to view this content.

Is supposed to crop the list to only show the values that exist and remove all whitespaces/empty entries in the lists (Rows 3-51). The strange thing is that it works for rows that have a certain amount (I think 5), but below that it will list the items in question along with the 40+ empty rows. I don't know why it is doing this.



The second issue is that I want to create a similar drop-down list using the named reference I'm using above, but I want to include two conditions instead of one. I am struggling with that as I am still quite new to this.

Wait... I might have actually thought of something.

Please Login or Register  to view this content.

Would something like this work?

your file will be more help

Originally Posted by tim201110

your file will be more help

I would share, but it's got very confidential data. Let me try and remove all non-relevant items and see.