Excel Named Ranges and Conditional Format


Joined
Feb 23, 2018
Messages
67
Reaction score
24
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.
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,511
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
 
Joined
Feb 23, 2018
Messages
67
Reaction score
24
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!!
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top