Conditional Format Explanation

[Arts]

Hi all

I can't see to grasp the concept as to why the conditional format works how it does.

I created a check box and a list of sales individuals (below), column F has a formula showing the max sales. When the check box for "Top Sales" is selected showing "TRUE"
I was hoping that both Column D and E would turn Green.

Within the conditional formatting I have put the following rule in =AND(E4=MAX($E$4:$E$9),$I$3). This doesn't seem to highlight both columns as I would like
What I don't seem to be understanding is that if I put a $ sign in front of the E4 so =AND($E4=MAX($E$4:$E$9),$I$3)

This then seems to highlight both columns and it is this that I can't seem to grasp why, if anyone can shed some light why that would be great.

Many thanks
Arts

 

[arthurbr]

You can find explanation at Excel cell references - relative & absolute references in conditional formatting rules

 

[Arts]

You can find explanation at Excel cell references - relative & absolute references in conditional formatting rules

Thanks I'll give it a once over, I am familiar with absolute cell references within formula's but this seems to have thrown as have highlighted both columns and if the value is the max then this should reflect.

But I'll see if the above link helps!

 

[Fluff]

As E4 is relative col D will compare E4 with $E$4:$E$9 but col E will compare F4 with $E$4:$E$9
By using E$4 both columns will compare E4 with $E$4:$E$9

 

[Arts]

As E4 is relative col D will compare E4 with $E$4:$E$9 but col E will compare F4 with $E$4:$E$9
By using E$4 both columns will compare E4 with $E$4:$E$9

I see, I didn't think there column reference would change as I had pasted the formula in to apply to those specific cells (if that makes sense). But understand what you mean, appreciate your response!

 

[Fluff]

Glad we could help & thanks for the feedback.