Unique values in dropdown without creating another list?

[Mr__P]

I have a dropdown that gets its reference indirectly from another cell.

The referenced list is a table column that will contain any digit from 1 to 9 but not necessarily all of them and some will appear more than once.

I can find lots of way to extract those unique values but This would mean creating another named range instead of just using the names of the table columns as the reference.

I'm hoping someone can work out maybe a nested formula to use in the data validation formula so my drop down just shows the unique available values.

I hope this makes sense.

Thanks

Mr__P

 

[Roderick_E]

=INDEX(List,MATCH(0,IF(ISBLANK(List),"",COUNTIF($B$1:B1,List)),0))

cse (paste in B2 down, list is any range)

 

[Mr__P]

=INDEX((INDIRECT("tracker["&$C$3&"]")),MATCH(0,IF(ISBLANK((INDIRECT("tracker["&$C$3&"]"))),"",COUNTIF($B$1:B1, (INDIRECT("tracker["&$C$3&"]")))),0))

This is what I have just tried in the data validation list formula box, swapping where it says "list" for the reference cell but it is only returning one value. Am I doing something wrong?

Thanks

 

[Roderick_E]

did you first get the list of unique items? That is what my code was trying to do for you. Once you get that, you can do something like:

=OFFSET(tracker!$C$3,0,0,COUNTIF(tracker!$C$3:$C$65536,"> ")) in the data validation box so it only shows the visible results

 

[Mr__P]

did you first get the list of unique items? That is what my code was trying to do for you. Once you get that, you can do something like:

=OFFSET(tracker!$C$3,0,0,COUNTIF(tracker!$C$3:$C$65536,"> ")) in the data validation box so it only shows the visible results

I was hoping. Got there would be a way to place the whole function into the validation box so I wouldn't need to have a separate list somewhere in the workbook.

Do you think it's possible?

Thanks

 

[Mr__P]

I was hoping there would be a way to place the whole function into the validation box so I wouldn't need to have a separate list somewhere in the workbook.

Do you think it's possible?

Thanks

(Phone autocorrect)