Defined Name used in Formula within Conditional Formatting

[msdclark]

Result I want - highlight any cell that has the matching text from a list on another sheet that has a defined name

I have selected a group of cells on a worksheet and named this "AgedInv"
In conditional formatting, I have placed this formula - =SUM(COUNTIF(A4,"*"&AgedInv&"*"))
Then I choose to have the formatting result in red font for matching the text in the defined name list

Either I haven't noticed this previously or something has changed, but all of a sudden there are inconsistencies in the data that is changed to red font. Some highlighted results are not on the Defined list and some on the defined list are not highlighted.

I attached sample workbook. Does anyone have any ideas about what the issue is?

[MarvinP]

Hi Danielle,

It looks like some of the stuff in your List Tab column A are numbers and not text. If you try to match those numbers to any text they won't agree. See the attached where I've added two columns to test column A. If that doesn't make sense then keep asking.

Text vs Number Lookup.xlsx

[6StringJazzer]

You are applying the CF condition to the entire column but your formula references A4. That means that to format A1, it will check A4; to format A2, it will check A5, and so on. Change A4 to A1 and your formula works.

[msdclark]

6StringJazzer - that worked. Sometimes the simple issues are the hardest to pinpoint.