Issue with relative conditional formatting

[MoogBot]

I'm having issues with relative conditional formatting

i would like to highlight the current price of a list of stocks when it's below my target price.
I've learned that you need to use the INDIRECT formula to point to the relative current and target prices (if you don't do this than you always compare within the first line in the list)

My formula's are correct, returning false and true, but when i incorporate them in conditional formatting, it just doesn't work.

Can someone help out?


The file can be downloaded here

 

[James006]

Hi,

For first formula :

=AND(W11>D11,W11>0)

For second formula

=AND(D11<=W11,W11<=D11*1.25)

HTH

 

[MoogBot]

Hi James,

Thank you for the suggestion... However I think i already have formulas that should work. I included them in the sheet with a ' to show them -->

First condition (to be highlighted in green)
=AND(INDIRECT("D"&ROW())>=(INDIRECT("W"&ROW())),(INDIRECT("W"&ROW())>0))

Second condition (to be highlighted in yellow)
=AND((INDIRECT("W"&ROW()))>(INDIRECT("D"&ROW())),(INDIRECT("W"&ROW()))<=((INDIRECT("D"&ROW()))*1.25))

i'm not referencing directly, but using indirect, because apparently conditional formatting has issues with relative references when used in a list (column). I thought these formulas would fix the issue, however they do not format the cells.....

 

[James006]

Hi,

From what I understand, there is no need for you to use Indirect() ...

Just apply the formulas to the whole list ... and relative references will adjust themselves ...

HTH

 

[MoogBot]

Hi James, You are correct.

Apparently, I was implementing it wrong by first applying conditional format to the first row and then adjusting the range to the list that it should apply to.