Validate Text for Pivot Table Filter
 

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

Validate Text for Pivot Table Filter
 

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

Validate Text for Pivot Table Filter
 

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