removing blanks and duplicates in drop down list

[zebrasam]

I have column A and B.

Column A has the name of a different animal in each cell.

In column B I need a drop down list in each cell. I would like to type the name of the species in the cells in column B corresponding to the name of the animal on the same row in column A.
This drop down list will grow in size as I work my way down the spreadsheet continually adding new species in the cells of column B. This needs to be done as I work my way down the spreadsheet and cannot be predetermined beforehand. (note: This is a hypothetical example to which my situation corresponds exactly)

I managed to get this working but the problem is that as the drop down list grows blanks and duplicates are added to the drop down list.

Sam

 

[T. Valko]

Also in my real spreadsheet I will need to apply this formula to multiple columns not only one.

Here you go.

DropDownList.xlsx 10kb

http://cjoint.com/?BIicMR1rufG

 

[zebrasam]

I get this error

Excel found unreadable content in 'BIicMR1rufG_dropdownlist.xlsx'. Do you want to recover this workbook?

If I say yes then it says this:

Removed Feature: Data validation from /xl/worksheets/sheet1.xml part


I tried inputting the data validation myself:


=IF(COUNTIF(D$2:D$20,"?*"),D$2:INDEX(D$2:D$20,COUNTIF(D$2:D$20,"?*")),NA())


but I get the following error:

You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data validition criteria.


I also got this same error before when I tried to make my own spreadsheet according to your instructions.

 

[T. Valko]

I get this error

Excel found unreadable content in 'BIicMR1rufG_dropdownlist.xlsx'. Do you want to recover this workbook?

If I say yes then it says this:

Removed Feature: Data validation from /xl/worksheets/sheet1.xml part


I tried inputting the data validation myself:


=IF(COUNTIF(D$2:D$20,"?*"),D$2:INDEX(D$2:D$20,COUNTIF(D$2:D$20,"?*")),NA())


but I get the following error:

You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data validition criteria.


I also got this same error before when I tried to make my own spreadsheet according to your instructions.

I'll send you a private message.

 

[Michael Feliciano]

I'll send you a private message.

I was hoping to see the resolve to this error message because I received it as well.

Here is what I am trying to do. I have several tabs on a worksheet. One tab is setup as my Legend tab where I house information for my validation drop down list.

On this legend tab I have a prospect list, column H. Each cell in column H is a reference to another tab. Basically it is transferring over a name of a client from one of the sales persons tab. Using this data on the Legend tab I was actually able to follow your formula until I got to the: Allow=List, Source: ='s the formula you mentioned and this is where I received the same error message the gent or lady received, You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria.

 

[Michael Feliciano]

I was hoping to see the resolve to this error message because I received it as well.

Here is what I am trying to do. I have several tabs on a worksheet. One tab is setup as my Legend tab where I house information for my validation drop down list.

On this legend tab I have a prospect list, column H. Each cell in column H is a reference to another tab. Basically it is transferring over a name of a client from one of the sales persons tab. Using this data on the Legend tab I was actually able to follow your formula until I got to the: Allow=List, Source: ='s the formula you mentioned and this is where I received the same error message the gent or lady received, You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria.

Here's is a link to my monster spreadsheet: https://dl.dropboxusercontent.com/u/42876390/Sales%20SOP.xlsm

 

[Michael Feliciano]

/\ /\ /\
Can anyone provide some assistance