Conditional Formatting colors between two tables

[learningstatistics]

Hello,

I have three tables.
Table 1 is the original table which is a table that shows years and criteria and in it we have colors where each color is used for a certain grade. So BLUE=GREAT, YELLOW=GOOD, RED=BAD, and white = NA.
Table 2 is derived from table 1 using a match function and a vlookup which uses column A of table 3. (Please see formula cells in table 2). So basically for every A,B,C (from table 3) and year you can see the color in a certain cell.
I want to use table 2 to fill in the colors in the corresponding cells of table 3 (I don't need to fill in BAD, GOOD, GREAT, NA in table 3, just the colors. I still want my numbers in table 3 to show too with the colors. So Cell of A and 2015 (which shows a 2 should be Yellow and (A,3) should be Red and so on.

Please see the pic attached.

Thank you!!!

 

[mart37]

Have table 2 and table 3 the same dimensions?
Are they on two sheets with the same structure?

 

[learningstatistics]

Hello,

Table 2 and table 3 are not exactly of the same dimension as I added a row for match on the top of table 2 to get the colors from table 1 to table2.
TAble 2 also uses a vlookup that depends on $A11 (Please see yellow cell with the formula) in table 2.

The tables are in the same sheet and they look just the same way. Table 3 is to the left of table 2. I appreciate your time. If you have any other question please let me know.

Thank you

 

[mart37]

Map3
	B	C	D	E	F	G
11	2	3			GOOD	BAD
12	5	3			GREAT	NA
13	2	9			NA	GOOD
Blad1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B11:C13	Expression	=OFFSET($A11,0,COLUMN()+3,1,1)="BAD"	text	NO
B11:C13	Expression	=OFFSET($A11,0,COLUMN()+3,1,1)="NA"	text	NO
B11:C13	Expression	=OFFSET($A11,0,COLUMN()+3,1,1)="GREAT"	text	NO
B11:C13	Expression	=OFFSET($A11,0,COLUMN()+3,1,1)="GOOD"	text	NO

 

[learningstatistics]

That works great! Thank you very much!!