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

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? ToolsOptionsCalculationAutomatic

--
Regards
Roger Govier

"tommcbrny" wrote in message
...
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

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

"Roger Govier" wrote:

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? ToolsOptionsCalculationAutomatic

--
Regards
Roger Govier

"tommcbrny" wrote in message
...
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