Create dynamic dropdown with no 0 entries

[Tom_T]

Hi

I'm trying to make a dropdown list that removes selected entries and doesn't show any 0 entries.
Someone on reddit posted the following:

Selections are done in column C with the dropdown list configured as =$H$6#
When an items is selected it is removed from the dropdown list.

The original code had the COUNTIF as C6:C11 and that worked great but didn't use all of the available dropdown options, I want to be able to select all options.
When I do that I get the #CALC! message, which then appears in the dropdown list.

Does anyone know how to do this correctly, so the cell in C can be empty or a value from the dropdown.

Thanks

 

[jasonb75]

The countif ranges are the wrong way around, it should be COUNTIF(F6:F14,C6:C18)

 

[Fluff]

Hi & welcome to MrExcel.
Do you mean that you simply want to get rid of the #Calc error?

 

[Fluff]

@jasonb75 wouldn't putting it that way give a #Value error?

 

[Fluff]

If you do just want to get rid of the error, you can use

=SORT(FILTER(F6:F14,COUNTIFS(C6:C18,F6:F14)=0,""))

which will leave you a blank value in the drop down.

 

[Tom_T]

If you do just want to get rid of the error, you can use

=SORT(FILTER(F6:F14,COUNTIFS(C6:C18,F6:F14)=0,""))

which will leave you a blank value in the drop down.

This does help but only if F6:F14 contain data. If I change this to F6:F100 then I get lots of 0 entries in the dropdown list which I'm trying to avoid.
Is there any way to do that ?

Thanks

 

[jasonb75]

@jasonb75 wouldn't putting it that way give a #Value error?

Ignore me people, I misread the formula and had a ? moment.

 

[Fluff]

This does help but only if F6:F14 contain data. If I change this to F6:F100 then I get lots of 0 entries in the dropdown list which I'm trying to avoid.

Then why didn't you say so to start with?
You can use

=SORT(FILTER(F6:F100,(COUNTIFS(C6:C18,F6:F100)=0)*(F6:F100<>""),""))

 

[Tom_T]

@Fluff Perfect that does what I need it to do

Thanks

 

[Fluff]

Glad we could help & thanks for the feedback.