Excel Named Ranges and Conditional Format

[Simont485]

Hi again

Apologies that I keep posting....I do know how to use excel but sometimes I cant understand it!!

SO I have a list of letters with a Full Stop after them(A. to L.) that I have named OTCODES. I use the same letters without the full stop but I want to Conditional Format (with a fill colour)the cells with a full stop when they are used.

I apply CF to one cell using a formula =countif(OTCODES,$A$1) and apply it to cell A1......and it works.

But when I try to apply the same CF to a range of cells, =countif(OTCODES,$A$1:$NA$100) and apply it to $A$1:$NA$100, it just applies the formatting to all cells in the range irrespective of content(inc. blank).

Where am I going wrong?

Many thanks in advance.

 

[Becky]

I think the problem stems from how you are using the COUNTIF formula - the syntax is as follows:

=COUNTIF(range,criteria)

So when you specify one cell in the criteria it works, but multiple cells cause problems.

What is it you're trying to make the CF highlight? There's a useful example of how COUNTIF can be used with CF here: https://exceljet.net/conditional-formatting-with-formulas

 

[Simont485]

HI Becky

I used Countif as, somehow, it worked on a previous spreadsheet??!??(odd that)

But having looked at the link I have got the required result.

If I use =sumproduct(isnumber(search(namedrange, cell)))>0 then apply to the range of cells CF works as required.

Thank you for your time and help, greatly appreciated as always!!

 

[Becky]

Brilliant, glad to hear you found a solution