Forum Discussion
Conditional formatting for entire row based on data in another row
Hello everyone
I have trouble finding a formula or condition, that will solve my issue.
Currently i have a file containing numbers, if the 2 previous numbers are equal to the 3rd number, then "yes". I've made a condition that should turn yes into yellow.
My issue is now that I need the 2 previous rows to get another color, and even, if possible, add another column with a text for me to filter it. so, it's possible for me to get a total of all "YES" and then the previous 2 rows in one file.
Thank you in advance,
You are welcome. In cell J13253 i've entered this formula and draged across column J.
=IF(OR(I13253="yes",OFFSET(I13253,1,0)="yes",OFFSET(I13253,2,0)="yes"),"X","")
6 Replies
Does this return your expected result?
=IFERROR(INDEX(C$5:C$23,SMALL(IF($F$5:$F$23="yes",ROW($C$5:$C$23)-4),ROW(A1))),"")This is the formula in cell H5. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
You can see the rules for conditional formatting in the attached file.
OliverScheurich
Perhaps, I'm just stupid, but I can't get it to work.
I'm currently using this for my yes/no calculation.=IF(C13253=C13251+C13252;"yes";"no")
(Sidenote, I can't get permission to upload pictures, so here I have a screenshot instead)
https://imgur.com/HqwZgHQ=$I3="yes"This is the rule for conditional formatting that is applied to three different ranges as shown in the screenshot and in the attached file.
In cell I13253 i've entered your formula and draged it across column I.
