TLDR; seems like CF thinks my formula with named range is an array, and I want it to function like a flat formula.
Long time lurker, first time poster!
I have been creating nice elaborate conditional formatting formulas for a while now. However, I have always had a challenge when using these with named ranges. The formulas work great when in a cell, but when placed into CF, they fail.
Perhaps my issue is related to the way in which CF seems to already identify these formulas as arrays. Perhaps somebody can help me understand how best to overcome this issue. I'll link a document for illustration.
So when I place escalating numbers in A1:J1, and place in A2:J2 this formula
=IF(TheNums>4,TRUE,FALSE)
then E2:J2 all come back TRUE. Placing the same formula into CF comes back all FALSE. If I enter the same formula as an array with CSE, I get FALSE all across.
Can somebody help with my syntax to hopefully stop this from failing?
Thanks so much!
Kyle
Best Answer
I think what you may need is to change the way the named range works. For example, if your named range returns more than one cell, the Conditional Formatting won't be able to handle it very easily (you would probably have to create a UDF in VBA). However, there might be a quicker/easier solution.
If your named range is this:
Change it to/add another one:
Make sure that you have the correct cell selected when you define a named range without an absolute reference. In this case you would need to select any cell in column A because the column reference is relative, not absolute. Now will you get a single cell reference back from the named range. It will always be a cell on row 1. This will make it easier for you do something with it in Conditional Formatting.