Conditional Formatting Based On Multiple Columns

Question

Sheet1

[

Sheet2 [

On Sheet 1 I have four columns Col A= 1st adjustment, Col B= 1st Check, Col C= 2nd Adjustment, Col D= 2nd Check.

The values in these columns are either 0 or non 0. Always a number. For the two adjustment columns I format them to be Red (if they are non-zero) and for the two check columns I format them to be Yellow (if they are non-zero).

On sheet 2 there is a column that pulls and combines the the sets of adjustments and checks and assigns them to the correct employee ID #.

Somehow I need the formatting on the column in sheet 2 to be red if the number came from an adjustment column on the other page and yellow if the number came from a check column on the other page. And no color if it is equal to 0.

What is the best way to do this? I have not been able to find a solution.

The conditional formatting rules I have are all applied to column C Sheet 2. They are:

#1- Cell value equal to = 0 (no fill)

#2 Cell value equal to =Sheet1!$E1 (yellow fill)

#3 Cell value equal to =Sheet1!$C1 (yellow fill)

#4 Cell value equal to =Sheet1!$D1 (red fill)

#5 Cell value equal to =Sheet1!$B1 (red fill)

Answer 1

Assuming that both lists start in A1, put this formula in cell D2 of Sheet2:

=IF(C2=0,0,IF(C2=INDEX(Sheet1!$B$2:$E$7,B2,(A2-1)*2+1),1,2))

Then select the range C2:C13 and open the conditional formatting menu. Add 3 new rules based on formula. The first formula is:

=D2=1

and set the format as filled in red. The second formula is:

=D2=2

and set the format as filled in yellow. The third formula is:

=D2=0

and set the format as unfilled (which is not the same as no format selected). This last rule is not strictly necessary if none is going to change the format of your range manually. Still...

Note that each formula for the conditional formatting is written without any $ that would lock the reference. Therefore each cell in the range $C$2:$C$13 will translate that formula relatively to its own position. Cell C2 will "look into" cell D2, cell C3 will look into cell D3, C4 into D4 and so on. You can of course hide column D and the conditional formatting will still work.

You could also make it without using an extra column. Just edit the formula meant for cell D2 accordingly and use it in your rules. Something like this should work:

=IF(C2=0,0,IF(C2=INDEX(Sheet1!$B$2:$E$7,B2,(A2-1)*2+1),1,2))=1
=IF(C2=0,0,IF(C2=INDEX(Sheet1!$B$2:$E$7,B2,(A2-1)*2+1),1,2))=2
=IF(C2=0,0,IF(C2=INDEX(Sheet1!$B$2:$E$7,B2,(A2-1)*2+1),1,2))=0

Formula details (as asked in comments)

B2 is for the row. In the context of the INDEX function it says at what row of the range ($B$2:$E$7 in the formula we see here) the INDEX function has to "look". If B2 is 1, it will consider the first row of the range (in our case row 2). If it's 2, it will consider the second row (in our case row 3).

(A2-1)*2+1 stands for the column. In the context of the INDEX function it says at what column of the range ($B$2:$E$7 in the formula we see here) the INDEX function has to "look". If the result is 1, it will consider the first column of the range (in our case the B column). If it's 2, it will consider the second column (in our case the C column).

About (A2-1)*2+1: let's change it a bit so it will be easier to visualize while i explain it:

1+(A2-1)*2

We can divide the formula in 2 parts: the 1 which stand as our starting point (the first column) and the (A2-1)*2+1 which deal with the shift in columns according to the shift in weeks. In cell A2 we find the number of the week. In the first sheet we have 2 columns for each weeks. Therefore when the week shifts by 1, our target column will have to shift by 2. We can't just multiply by 2 the week sheet because it's the shift from the first week that counts for 2, not the actual value. At the first week we will have no shift. Therefore our shift will have to be 0; but we can't just use a 0 because there is no "zero column" in a range. Therefore we need to have our starting value of 1 that is summed to the shift. This way for the first week we will have:

1+(1-1)*2

1+(0)*2

1+0

1

First column of the range $B$2:$E$7 (which is column B). For the second week we will have:

1+(2-1)*2

1+(1)*2

1+2

3

Third column of the range $B$2:$E$7 (which is column D).

Over all the formula IF(C2=INDEX(Sheet1!$B$2:$E$7,B2,(A2-1)*2+1),1,2) basically checks if the value in C2 is the same as the first column of the given week. If it's the same value it returns 1, otherwise it returns 2 (basically assuming that the value will be equal to the one in second colum of the given week).

Report any question you have or bug you have encountered. If, according to your judgment, this answer (or any other) is the best solution to your problem you have the privilege to accept it (link).