Hi All,
I'm struggling with a query on Excel.
I have some cells which i would like to change colour to Red amber and Green depending on where the number falls within a reference table
So my information table will automatically change Cell A2, B2, C2, D2 to red, amber or green depending on where that number falls within table 2 - Reference data
Table 1
<tbody>
</tbody>
Table 2
<tbody>
</tbody>
I have 3 formulas in conditional formatting for each of the four cells in table 1. I've been using:
For cell A2
=and(A2>=B3,A2<=C3) Colour Red
=and(A2>=D3,A2<=E3) Colour Amber
=and(A2>=F3,A2<=F3) Colour Green
And i do this 3 times for each of the cells in the information table 1 = A2, B2, C2 & D2.
This works sometimes but not always and i'm totally confused as to why that is!
Any help will be welcomed. Thanks in advance
I'm struggling with a query on Excel.
I have some cells which i would like to change colour to Red amber and Green depending on where the number falls within a reference table
So my information table will automatically change Cell A2, B2, C2, D2 to red, amber or green depending on where that number falls within table 2 - Reference data
Table 1
A | B | C | D | |
1 | Temple | Sun | Chapel | Yard |
2 | 1 | 5 | 8 | 10 |
<tbody>
</tbody>
Table 2
A | B | C | D | E | F | G | H | |
1 | RED | AMBER | GREEN | MAX | ||||
2 | From | To | From | To | From | To | ||
3 | Temple | 0 | 3 | 4 | 6 | 7 | 10 | 10 |
4 | Sun | 0 | 3 | 4 | 5 | 6 | 11 | 11 |
5 | Chapel | 0 | 4 | 5 | 7 | 8 | 12 | 12 |
6 | Yard | 0 | 8 | 9 | 15 | 16 | 23 | 23 |
<tbody>
</tbody>
I have 3 formulas in conditional formatting for each of the four cells in table 1. I've been using:
For cell A2
=and(A2>=B3,A2<=C3) Colour Red
=and(A2>=D3,A2<=E3) Colour Amber
=and(A2>=F3,A2<=F3) Colour Green
And i do this 3 times for each of the cells in the information table 1 = A2, B2, C2 & D2.
This works sometimes but not always and i'm totally confused as to why that is!
Any help will be welcomed. Thanks in advance