Data validation error "the source currently evaluates to an error" using Filter function

[reg000]

Hi All,
I'm creating a data validation source list linked to a name =FG.
The name "FG" definition is an unique list created by the following function: =SORT(UNIQUE(FILTER('FCST BW'!$P:$P;NOT(ISBLANK('FCST BW'!$P:$P))*NOT(ISERROR('FCST BW'!$P:$P)))))
It basically includes only unique names (not blank and without errors) and sort them a to z from a selected column "P", the result string is correct with the classic format: {"a";"b";"c" .....}.

when i'm trying to add the name in a data validation it returns the error "the source currently evaluates to an error".

Any Guess on how to solve it?

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
Why not put the formula in a cell & use that in the DV?

 

[reg000]

Hi & welcome to MrExcel.
Why not put the formula in a cell & use that in the DV?

Hi Fluff,
That would work for sure, i was just wondering why the DV on a correct list is returning an error when using the same list with an OFFSET is working... also i old like to reduce addition sheets or cells that can be edit from final users.

 

[Fluff]

I've no idea why, but for some reason DV does not like defined names with that type of formula.

 

[reg000]

unfortunately that's the best solution... not the first time Excel don't like a formula, won't be the last