Why doesn't work FILTER function in data validation list?

Question

I want to use this formula in data validation list: (A7 cell)

=FILTER($A$1:$A$5;ISNUMBER(SEARCH(E1;$A$1:$A$5));"not found")

I got "The source currently evaluates to an error." message.

I need to drag this formula to get for E2, E3 etc.

Answer 1

On Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20332) 32-bit it still evaluates to an error for some reason, using F3#, however the offset one does work. Enterprise never seizes to disappoint!

Answer 2

Unfortunately there is no way to enter such a function directly within validation list dialogue / source.

Fortunately there are a couple of workarounds - I provide the simplest that does not require VB.

Step 1) Create single helper eqn: using filter function provided

=FILTER(B3:B6,--ISNUMBER(SEARCH(G2,B3:B6)))

---

Step 2) Create named range referring to (1)

Create dynamic named range (Formulas-> Name Manager -> New) -- here: name:= 'LIST' and function/refers to:=

F3#

This will work given you must have Office 365 compatible version of Excel to use Filter function in first place…

Note: For those who have a similar enquiry {referencing range based upon function in validation list 'source') - but are not using Office 365 Excel version functions - you can proceed with same steps but modify Step 2 named range function as follows:

F3:offset(F3, counta(F3:F1000)-1,0,1,1)

making sure no 'obstructions' in column containing helper function - which is advisable for Office 365 users too.

---

Step 3) Proceed with Validation List

Insert validation list by referencing new name in 2:

---

Sample usage

Voila!

---