conditional formatting based of sum of CHANGING range

[thegurumonkey]

Hi

This is some very weird behaviour. A formula shows the correct 'TRUE/FALSE' in a helper column, but doesn't work in conditional formatting!

i'm trying to colour items in a column from the row of today (whose number is in $b$1) to the row where we run out of stock (number of items in stock defined in rr$1)

My initial effort was:

=SUM(INDEX(RR:RR,$B$1):RR159)<RR$1

is the version of the formula applied to cell rr159

which does the trick in cells, but won't work in conditional formatting. a warning comes up, but i don't understand which part of this type of formula it won't accept.


Anyway, though i usually try to avoid indirect, i got it to work for this column with:

=SUM(INDIRECT("rr"&$B$1&":"&"rr"&ROW(rr159)))<rr$1

but then when i made it reference the column letters (in row 255) so it could be applied across multiple columns thusly:

=AND( SUM(INDIRECT(rr$255&$B$1&":"&rr$255&ROW(rr159)))<rr$1 ,COUNTBLANK(rr$1)=0)

it applies said formula to the whole column, despite the same formula showing the correct '"TRUE/FALSE" values when applied to a helper column beside the data...

I'm stumped... any help muchos appreciatos

 

[CyrusTheVirus]

might want to take a look at your absolute reference compared to mixed references...

example: =SUM(INDEX(RR:RR,$B$1):RR159)<rr$1
<--- should this be B1 instead of $B$1?? I don't know, just a thought.</rr$1

 

[thegurumonkey]

Thanks for the suggestion, but that won't it. It looks wrong at a glance, but b1 has the row number of today on it, so it may be an absolute reference.
All the $ are right. As i said, the formula actually works when played into a helper column. Something wierd in conditional formula.
Any more for any more? Thanks

 

[CyrusTheVirus]

=SUM(INDEX(RR:RR,$B$1):RR159)<rr$1

is the version of the formula applied to cell rr159</rr$1

isn't that circular referencing?

 

[thegurumonkey]

It's circular refetencing even possible for conditional formatting? Each cell often references the value in itself in order to define the format.
That said, oops, I have failed to paste the whole formula. My bad.

The initial formula should have been
=SUM(INDEX(RR:RR,$B$1):RR159)<rr$1

and hence generates a true or false rather than a number.
When entered as a conditional formatting formula, it says "you can't use ranges or [something] or [something else] in conditional formatting."
I'm in bed now or I'd quote the warning pop-up more exactly for you. Sorry