Filter not working for #NAME error

[bobby_smith]

Hi Guys,

I'm currently experiencing a strange situation when I filter a column for #NAME it returns blank lines. The item is in the column but for some reason the filter is not returning it. You can also see in the filter drop down.

My current workaround was to use find and replace and replace all #NAME with another word and then the filter works.

I'm trying to avoid having to do this each time.

Thanks in advance.

 

[Fluff]

Set the filter to Equals #NAME ?
If you select it from the dropdown list it will filter for #NAME ~? which doesn't work.

 

[bobby_smith]

It worked. WoW. Thanks.

Do you know why excel is searching #NAME ~? and not #NAME ?.

 

[Fluff]

A question mark is used as a wildcard so when looking for a value that contains ? you need to precede it with ~, otherwise Xl thinks you're using it as a wildcard.
So when you set the filter from the dropdown it very helpfully adds the ~. Unfortunately as you are working with an error message that doesn't work.