How To Ignore Duplicates and Blanks in a Drop Down list in Excel Or Name Manager Tool

[lqorri]

Hi All,

Can someone please explain how to ignore duplicates and blank cells in name manager tool by inserting a simple formula or in the data validation window? I have read through the different formulas such as the below:

=OFFSET('Raw Data'!$A$2:$A$30,15,0,COUNTA('Raw Data'!$A:$A),1)

provided on different threads but have not worked for me. I tried a pivot table to get rid of the duplicates but blanks show up when I try to create a drop down list. Any help would be greatly appreciated. I have attached an excel file as well.

Thank you

[TheN]

what exactly are you looking for? a dynamic named range?

I have no idea what you are asking for, since your description wasn't very clear. Would it be something like a dynamic named range which only gives the unique values in a column? Please elaborate on what your desired results are, thanks.

is it something like this?

http://www.excelforum.com/showthread...=1#post4467192

[lqorri]

Hi TheN,

Thank you for your quick reply. The link you have provided is what I am looking for but is there an easier way without adding different columns to get the desired results? Basically what I am looking for is being able to insert maybe a formula or vba code in the data validation window which can ignore either the duplicate cells or the blanks. the ignore blanks option in the data validation does not work and the formula I provided does not work either. Either or of ignore blanks or duplicates can work for me. I can not use the attached link you send because I will be creating a large database and having as less columns as possible would be great. Does this help? did you get a chance to look at my excel file?

Column
Luis
Luis

George
George

[TheN]

It's the same thing. All you need to do is add a third layer (I believe you want three inter-related lists?). The extra sheet marked as "Lists" can simply be hidden, and you can just have your drop-down lists on a third sheet.

If your lists are bigger, all you need to do is drag down the formulas.

So to add a third list, all you have to do is add a third column, and make a third unique list.

To see the named ranges, go to to formulas tab and click name manager. Copy the loser list and its unique counterpart into a new named group, then change the b references to c. Then, copy over the formulas in the Lists tab to make a third column and replace the references accordingly. Let me know if you have any questions.

[lqorri]

I understand the extra layer and i might have to do it that way but I just want to make sure my question is clear enough because i might no be wording it well. I am working on building a numbering protocol, meaning i will have to create possibly three categories with three drop down list options. For Example: once you select commodity, the second option will be perishables, non perishables and semi perishables. if you select perishables it will give you Meats, Fish, Etc. I expect the categories to increase in the future and i wanted to see if there was an easier way by inserting a formula (name manager & data validation) that will take care of it. i guess with your method i would need to add an extra column with the formulas for each category.

Master Category Category Sub Category
Commodity Perishables Meats
Perishables Fish
Perishables Eggs
Perishables Fruits
Perishables Vegetables
Semi Perishables Cereals
Semi Perishables Pulses
Semi Perishables Fats
Semi Perishables Oils
Semi Perishables Condiments
Semi Perishables Spices
Semi Perishables Flour
Non Perishables Salts
Non Perishables Mild Acids
Non Perishables Sugar

[TheN]

The option I gave you is definitely the best option for that. It is called a DYNAMIC named range for a reason, and that reason is that the range automatically adjusts as you add more data (so long as it is under 1000 lines, if not, make your range larger initially). So the only thing you'd ever have to adjust manually after adding in more data is the amount of cells with the array formulas in them on the "Lists" page if your amount of unique items ever exceeded that amount. And in that case, all you have to do is select the bottom one and drag down.

Also, you could have the third list be an output if you so desired, and surround the formula with another function to eliminate the #N/A errors.

Please Login or Register  to view this content.

Moreover, depending on what you wanted the list(s) for, you could draw other information from the changing lists too. It all depends on what you want to get out of your file.

[lqorri]

Thank you for your help. it worked.