=ADDRESS(ROW(),COLUMN())=$D$12
Daglig Styrning.xlsx | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | |||
2 | Idag | ROW | 9 | |||||||||||||||
3 | 23 | CELL | 27 | |||||||||||||||
4 | Address | AA$9 | Cell that should be formatted this day with a colored frame | |||||||||||||||
5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | ||||||||
6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||
8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 3 | 0 | 0 | ||||
9 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | 2 | 0 | 4 | 0 | 0 | 0 | 0 | ||||
10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||
11 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
12 | ||||||||||||||||||
13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
17 | 0 | 0 | 19 | 0 | 0 | 0 | 0 | |||||||||||
18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||||
Settings |
Cell Formulas | ||
---|---|---|
Range | Formula | |
P3 | P3 | =DAY(TODAY()) |
R2 | R2 | =MAX(Q5:W11) |
R3 | R3 | =MAX(Q13:W19)+8 |
R4 | R4 | =ADDRESS(Settings!R2,Settings!R3,2) |
Q5:W11 | Q5 | =IF(I5=$P$3,ROW(),0) |
AA5 | AA5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],1)) |
AB5 | AB5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],2)) |
AC5 | AC5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],3)) |
AA6 | AA6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],4)) |
AB6 | AB6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],5)) |
AC6 | AC6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],6)) |
Y7 | Y7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],7)) |
Z7 | Z7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],8)) |
AA7 | AA7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],9)) |
AB7 | AB7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],10)) |
AC7 | AC7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],11)) |
AD7 | AD7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],12)) |
AE7 | AE7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],13)) |
Y8 | Y8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],14)) |
Z8 | Z8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],15)) |
AA8 | AA8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],16)) |
AB8 | AB8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],17)) |
AC8 | AC8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],18)) |
AD8 | AD8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],19)) |
AE8 | AE8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],20)) |
Y9 | Y9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],21)) |
Z9 | Z9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],22)) |
AA9 | AA9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],23)) |
AB9 | AB9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],24)) |
AC9 | AC9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],25)) |
AD9 | AD9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],26)) |
AE9 | AE9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],27)) |
AA10 | AA10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],28)) |
AB10 | AB10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],29)) |
AC10 | AC10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],30)) |
AA11 | AA11 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],31)) |
Q13:W19 | Q13 | =IF(I5=$P$3,COLUMN(),0) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
AA5:AC11,AD7:AE9,Y7:Z9 | Cell Value | =0 | text | NO |
AA5:AC11,AD7:AE9,Y7:Z9 | Cell Value | =1 | text | NO |
AA5:AC11,AD7:AE9,Y7:Z9 | Cell Value | =4 | text | NO |
AA5:AC11,AD7:AE9,Y7:Z9 | Cell Value | =3 | text | NO |
AA5:AC11,AD7:AE9,Y7:Z9 | Cell Value | =2 | text | NO |
Y5:AE11 | Expression | =ADDRESS(ROW();COLUMN())=#REF! | text | NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D5 | D5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],1)) |
E5 | E5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],2)) |
F5 | F5 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],3)) |
D6 | D6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],4)) |
E6 | E6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],5)) |
F6 | F6 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],6)) |
B7 | B7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],7)) |
C7 | C7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],8)) |
D7 | D7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],9)) |
E7 | E7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],10)) |
F7 | F7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],11)) |
G7 | G7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],12)) |
H7 | H7 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],13)) |
B8 | B8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],14)) |
C8 | C8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],15)) |
D8 | D8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],16)) |
E8 | E8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],17)) |
F8 | F8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],18)) |
G8 | G8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],19)) |
H8 | H8 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],20)) |
B9 | B9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],21)) |
C9 | C9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],22)) |
D9 | D9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],23)) |
E9 | E9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],24)) |
F9 | F9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],25)) |
G9 | G9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],26)) |
H9 | H9 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],27)) |
D10 | D10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],28)) |
E10 | E10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],29)) |
F10 | F10 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],30)) |
D11 | D11 | =MAX(MAXIFS(GreenCross[TypID],GreenCross[Aktuell],"Ja",GreenCross[Dag],31)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
B5:H11 | Expression | =ADDRESS(ROW();COLUMN())=Settings!$R$4 | text | NO |
D5:F11,G7:H9,B7:C9 | Cell Value | =0 | text | NO |
D5:F11,G7:H9,B7:C9 | Cell Value | =1 | text | NO |
D5:F11,G7:H9,B7:C9 | Cell Value | =4 | text | NO |
D5:F11,G7:H9,B7:C9 | Cell Value | =3 | text | NO |
D5:F11,G7:H9,B7:C9 | Cell Value | =2 | text | NO |
Daglig Styrning.xlsx | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
2 | Idag | ROW | 9 | ||||||||||||||
3 | 23 | CELL | 4 | ||||||||||||||
4 | Address | D$9 | |||||||||||||||
5 | 1 | 2 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
6 | 4 | 5 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
7 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
8 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||
9 | 21 | 22 | 23 | 24 | 25 | 26 | 27 | 0 | 0 | 9 | 0 | 0 | 0 | 0 | |||
10 | 28 | 29 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||
11 | 31 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |||||||||
12 | |||||||||||||||||
13 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
14 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
16 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
17 | 0 | 0 | 19 | 0 | 0 | 0 | 0 | ||||||||||
18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
19 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | ||||||||||
Settings |
Cell Formulas | ||
---|---|---|
Range | Formula | |
P3 | P3 | =DAY(TODAY()) |
R2 | R2 | =MAX(Q5:W11) |
R3 | R3 | =MAX(Q13:W19)-15 |
R4 | R4 | =ADDRESS(Settings!R2,Settings!R3,2) |
Q5:W11 | Q5 | =IF(I5=$P$3,ROW(),0) |
Q13:W19 | Q13 | =IF(I5=$P$3,COLUMN(),0) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
I5:O11 | Expression | =$P$3 | text | NO |
I showed you how to do that in my very first reply. Did that not work for you?The only question I have is if the Conditional Formatting could understand if it looks in B23 and use that address in that cell and then color the border of that cell (G8).
=ADDRESS(ROW(),COLUMN())=$B$23