Conditional formatting formula for MAX value across filtered table in Google Sheets

1
votes

A friend of mine is big into Final Fantasy XIV and asked me to build him a spreadsheet that would help him build a better gear set. I'm not very experienced with Google docs, so please forgive me if I have something mistaken.

https://drive.google.com/open?id=1loz2ARF42Dr_2sNqB4bGNkCbJ-TXnsD7o5LJ6RAmomQ

Objective: Select "head" and "strength" and ONLY the highest strength value from all of the 'head' slots would be highlighted.

The formula I am using is Format cell if Greater than or equal to 

=if(and(and($B$2<>"",$F$2<>""),MAX(B11,B29,B46,B63)),0)

(if the 'head' and 'strength' boxes are filled in, return the highest value of the list, then highlight the box if it is greater or equal to that value) and this rule is applied to each of the 'head' boxes.

I know this formula works, however, I am having an odd issue. I have been using conditional formatting to apply the above formula to each "head - strength" box, but when you update a different value to be higher, the previously highest value remains highlighted, even if you change the current highest value to be lower, it remains highlighted.

example: currently box b63 is the highest of the 4 values, if you change any of the other values to a high number (900) the box you changed will become highlighted, but box 63 still remains highlighted, even though it is no longer the highest value, even when changing b63 to 0, the box remains highlighted.

This method does work if the 'conditional formatting' rule is applied to each box INDIVIDUALLY, but with > 200 boxes, that is incredibly tedious. I'm wondering if I have somehow gotten the formula mistaken?

Otherwise, if there is another solution or way to do this, I'm open to suggestions. Thank you for your time.

1
google-sheetsfiltermaxgoogle-sheets-formulags-conditional-formatting

1 Answers

0
votes

use:

=(B11=MAX(FILTER(INDIRECT(ADDRESS(11,          
 MATCH(INDEX(FILTER($E$2:$E$7, $F$2:$F$7="x"), 1, 1), $A$10:$G$10, 0))&":"&
 ADDRESS(ROWS($A:$A), 
 MATCH(INDEX(FILTER($E$2:$E$7, $F$2:$F$7="x"), 1, 1), $A$10:$G$10, 0))), $A$11:$A=
 INDEX(FILTER({$A$2:$A$7;$C$2:$C$7}, {$B$2:$B$7;$D$2:$D$7}="x"), 1, 1))))*($A11=
 INDEX(FILTER({$A$2:$A$7;$C$2:$C$7}, {$B$2:$B$7;$D$2:$D$7}="x"), 1, 1))

0

spreadsheet demo