Using FILTER with COUNTIF

[Raddle]

Hi - hoping someone can put me out of my misery here.

I am trying to hide values from a drop-down if they have already been used. I mean, super-simple I know but I am clearly just not up to it.

So far I can get the =COUNTIF to return the TRUE/ FALSE results with this

=COUNTIF(Questions!$C$6:$C$46,Picklist!$C$6:$C$46)=0

but as soon as put the filter round it, I just get VALUE error.

There are two tabs, one called Questions and one called Picklist. No table at this point.

Can anyone see the issue?

Finally, what I really wanted was to be able to use table column headers for this but ... baby steps.

Thanks in advance ...

 

[Jeffrey Mahoney]

Does this example help

Book2
	D	E	F	G	H
1	Full List		Chosen Already		Filter
2	1		2		1
3	2		5		3
4	3				4
5	4				6
6	5
7	6
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=FILTER(D2:D7,COUNTIF(F2:F3,D2:D7)=0,"")
Dynamic array formulas.

 

[Raddle]

Hey - thank you for that. Yes it helped. Another issue I was having was that the data (which was actually quite long text entries) was too long in some cases and was breaking the whole thing. So having trimmed it, we are back in the game!!

Thanks again.