Validate Text for Pivot Table Filter

[tommcbrny]

Hello,

I have source data for a pivot table that can contain one or more values
"alpha", "beta", "gamma", "delta" (ex: cell can contain "alpha", "alpha"
"beta", "delta" "gamma", and so on).

I tried a formula to display "yes" if the source cell contained "alpha" and
"no" otherwise as follows:
=if(isnumber(search("alpha",a1)),"yes","no")

Unfortunately, this returns "yes" in my destination cell whether the source
cell contains "alpha" or not.

I found this formula in another post on filtering pivot tables but can see
that I am doing something wrong. I just don't know what. I tried "istext"
in place of "isnumber" with the same result, a "yes" is returned regardless
of whether "alpha" is in the source cell.

Can someone tell me what I am doing wrong or whether there is a better way
to accomplish this?

Thank you,

Tom

 

[Roger Govier]

Hi Tom

The formula is fine and does produce the correct results.
Are you sure you don't have $A$1 in the actual formula you are using?
Is calculation mode set to Automatic? Tools>Options>Calculation>Automatic

 

[tommcbrny]

Hi Roger,

Thanks for the valdiation. I tried again after reading your post and Excel
hung on me. I killed and restarted and the formula works fine now.
Calculation is set to Automatic as well, thanks for the suggestion as I was
unaware of that requirement. I don't know if it was or was not before the
hang.

Thanks again,
Tom