Danno,
Yes, you can write a CF formula that considers a specific value found anywhere within a defined or named range. To do this use COUNTIF in your CF formula: =COUNTIF(Range address or range name,"value")>0. In your example if the value you use for the warning is "QZ" and the range inspected is C10:C100 (which can also be named something like "NetReturn"), then you would format the warning cell conditionally using "Formula Equals" and =COUNTIF(NetReturn,"QZ")>0. If the range is not named, use =COUNTIF(C10:C100,"QZ")>0. Set formats accordingly and it lights up anytime "QZ" appears at least once in the target range. The range can be specified with cell addresses or with a Range Name, mentioned only because range naming is almost always the more pleasant approach.
Your task can be also be done in a "two-step" manner whereby the first step is to use a simple COUNTIF formula normally, directly (not as conditional formatting) in a nearby cell, let's say B1, using =COUNTIF(Range address or Rangename,"value"). This step simply exposes the number of times the warning value appears in the target range. The second step would then be to use a CF formula that looks only at that nearby count. For example, in cell A1, format conditionally with "Formula equals" and =B1>0.
In all conditional formatting applications, watch out for Absolute versus Relative addressing. Excel automatically structures it as Absolute addressing and I almost always end up modifying it to all or partly relative. By the way, for financial scenarios these days, you might want to expand the range of values that will result in the idiot warning.
Yes, you can write a CF formula that considers a specific value found anywhere within a defined or named range. To do this use COUNTIF in your CF formula: =COUNTIF(Range address or range name,"value")>0. In your example if the value you use for the warning is "QZ" and the range inspected is C10:C100 (which can also be named something like "NetReturn"), then you would format the warning cell conditionally using "Formula Equals" and =COUNTIF(NetReturn,"QZ")>0. If the range is not named, use =COUNTIF(C10:C100,"QZ")>0. Set formats accordingly and it lights up anytime "QZ" appears at least once in the target range. The range can be specified with cell addresses or with a Range Name, mentioned only because range naming is almost always the more pleasant approach.
Your task can be also be done in a "two-step" manner whereby the first step is to use a simple COUNTIF formula normally, directly (not as conditional formatting) in a nearby cell, let's say B1, using =COUNTIF(Range address or Rangename,"value"). This step simply exposes the number of times the warning value appears in the target range. The second step would then be to use a CF formula that looks only at that nearby count. For example, in cell A1, format conditionally with "Formula equals" and =B1>0.
In all conditional formatting applications, watch out for Absolute versus Relative addressing. Excel automatically structures it as Absolute addressing and I almost always end up modifying it to all or partly relative. By the way, for financial scenarios these days, you might want to expand the range of values that will result in the idiot warning.
Last edited: