Conditional format cells in spilled ranges as formulas

[Luggruff]

Hi!

I recently learned that I can conditionally format a range by using the formula =ISFORMULA(topLeftCellOfRange), though, I also then learned it doesn't work on spilled ranges, like when spilling with the =UNIQUE formula. See https://imgur.com/HpWOxSj or attached image:
Note: Column M does have values in the purple cells, I just censored them. Then, all subsequent columns (N:Y) are looking up data by matching with M5#

As you can see, the ISFORMULA formula is only applied to row 5 as that is the only one that actually has a formula.

The reason I cannot use the rule "No blanks" is because I am actually forcing blanks and if I do, then S5 will be white as it is a blank technically. Also, my range varies all the time, so it can extend longer than row 8. In S5 my formula is basically: =IF(formula=0%,"",formula) so that I can hide the data label in the stacked column chart that I am using my data table for.

Any suggestions of an alternative formula to use in the conditional formatting, so that Excel understands that it should actually color the SPILLED range? In other words, not just N5:Y5, but N5:Y8?

[#2 · Accepted solution]

@Luggruff 

Your formula doesn't make the cell blank, it returns empty string. So far in Excel formulas don't return blank as a value.

Thus if the purpose is to color all cells with any value, includes empty string, you may apply rule like

=ISTEXT(N5) + (LEN(N5)>0)

[#3]

@Luggruff 

Another approach is to test whether each conditionally formatted cell intersects the spilt range or not.  That is, to use a conditional format based upon the formula 

= isDA?

which refers to

= ISREF(thisCell spilt.range#)

where

'thisCell' is a relative reference to the current cell (=RC in R1C1 notation)

'spilt.range' is the anchor cell of the calculation range.

[#4]

@Sergei Baklan that absolutely did the trick! Will have to remember this. So any cell that is text, and any cell that has text (because that is how Excel reads it/what I tell Excel to output with "") with a length of 0 characters ("") - correct?

[#5]

@Luggruff 

I'd say any cell with any value except empty string (len>0) OR empty strings (for it len=0 but it's the text )