if statement using "today"

[ginstermanuk]

I am constructing a dashboard but i want a category to be dependent on 4 columns on a separate data input tab with a number of criteria...
So.. the dashboard tab updates from the "data input" tab depending on the date.

i have 5 columns in the data input tab

Date Col A Col B Col C Col D

What i want is for a box on the dashboard to say RED, AMBER or GREEN depending on the following criteria
If (A is 4 )or (B is 3 or more) or (C has been greater than 10 for the last 3 days) or (D has been greater than 20 for the last 3 days) then "RED
If (A is 3) or (B is 1 or 2) or (C has been greater than 5 but less than 10 for the last 3 days) or (D has been greater than 10 but less than 20 for the last 3 days) then "AMBER"
If (A is 2) or (B is 0) or (C is less than 5 for the last 3 days) or (D has been less than 10 for the last 3 days) then "GREEN"

Any help would be greatly appreciated

Thanks

 

[ginstermanuk]

Screenshot with data is attached

 

[petertenthije]

Copy the entire formula and paste it directly in the formula bar, not in the cell itself.
If you paste it in the cell the formula will probably be copied over into seperate rows.


=IF(OR(
INDEX($B:$B;MATCH(TODAY();$A:$A;0);1)=4;
INDEX($C:$C;MATCH(TODAY();$A:$A;0);1)>=3;
MIN(INDIRECT("$D$"&MATCH(TODAY();$A:$A;0)-2&":$D$"&MATCH(TODAY();$A:$A;0)))>10;
MIN(INDIRECT("$E$"&MATCH(TODAY();$A:$A;0)-2&":$E$"&MATCH(TODAY();$A:$A;0)))>20);"red";
IF(OR(
INDEX($B:$B;MATCH(TODAY();$A:$A;0);1)=3;
INDEX($C:$C;MATCH(TODAY();$A:$A;0);1)>=1;
MIN(INDIRECT("$D$"&MATCH(TODAY();$A:$A;0)-2&":$D$"&MATCH(TODAY();$A:$A;0)))>5;
MIN(INDIRECT("$E$"&MATCH(TODAY();$A:$A;0)-2&":$E$"&MATCH(TODAY();$A:$A;0)))>10);"amber";
"green"))

 

[ginstermanuk]

Copy the entire formula and paste it directly in the formula bar, not in the cell itself.
If you paste it in the cell the formula will probably be copied over into seperate rows.


=IF(OR(
INDEX($B:$B;MATCH(TODAY();$A:$A;0);1)=4;
INDEX($C:$C;MATCH(TODAY();$A:$A;0);1)>=3;
MIN(INDIRECT("$D$"&MATCH(TODAY();$A:$A;0)-2&":$D$"&MATCH(TODAY();$A:$A;0)))>10;
MIN(INDIRECT("$E$"&MATCH(TODAY();$A:$A;0)-2&":$E$"&MATCH(TODAY();$A:$A;0)))>20);"red";
IF(OR(
INDEX($B:$B;MATCH(TODAY();$A:$A;0);1)=3;
INDEX($C:$C;MATCH(TODAY();$A:$A;0);1)>=1;
MIN(INDIRECT("$D$"&MATCH(TODAY();$A:$A;0)-2&":$D$"&MATCH(TODAY();$A:$A;0)))>5;
MIN(INDIRECT("$E$"&MATCH(TODAY();$A:$A;0)-2&":$E$"&MATCH(TODAY();$A:$A;0)))>10);"amber";
"green"))

Thanks for this, just pasted it in the the formula bar and i get an error "We found a problem with this formula"

 

[petertenthije]

Could you try this? I replaced all semicolons with commas. The joys of different Excel versions.

=IF(OR(
INDEX($B:$B,MATCH(TODAY(),$A:$A,0),1)=4,
INDEX($C:$C,MATCH(TODAY(),$A:$A,0),1)>=3,
MIN(INDIRECT("$D$"&MATCH(TODAY(),$A:$A,0)-2&":$D$"&MATCH(TODAY(),$A:$A,0)))>10,
MIN(INDIRECT("$E$"&MATCH(TODAY(),$A:$A,0)-2&":$E$"&MATCH(TODAY(),$A:$A,0)))>20),"red",
IF(OR(
INDEX($B:$B,MATCH(TODAY(),$A:$A,0),1)=3,
INDEX($C:$C,MATCH(TODAY(),$A:$A,0),1)>=1,
MIN(INDIRECT("$D$"&MATCH(TODAY(),$A:$A,0)-2&":$D$"&MATCH(TODAY(),$A:$A,0)))>5,
MIN(INDIRECT("$E$"&MATCH(TODAY(),$A:$A,0)-2&":$E$"&MATCH(TODAY(),$A:$A,0)))>10),"amber",
"green"))

 

[ginstermanuk]

Could you try this? I replaced all semicolons with commas. The joys of different Excel versions.

=IF(OR(
INDEX($B:$B,MATCH(TODAY(),$A:$A,0),1)=4,
INDEX($C:$C,MATCH(TODAY(),$A:$A,0),1)>=3,
MIN(INDIRECT("$D$"&MATCH(TODAY(),$A:$A,0)-2&":$D$"&MATCH(TODAY(),$A:$A,0)))>10,
MIN(INDIRECT("$E$"&MATCH(TODAY(),$A:$A,0)-2&":$E$"&MATCH(TODAY(),$A:$A,0)))>20),"red",
IF(OR(
INDEX($B:$B,MATCH(TODAY(),$A:$A,0),1)=3,
INDEX($C:$C,MATCH(TODAY(),$A:$A,0),1)>=1,
MIN(INDIRECT("$D$"&MATCH(TODAY(),$A:$A,0)-2&":$D$"&MATCH(TODAY(),$A:$A,0)))>5,
MIN(INDIRECT("$E$"&MATCH(TODAY(),$A:$A,0)-2&":$E$"&MATCH(TODAY(),$A:$A,0)))>10),"amber",
"green"))

Thanks very much for your work on this, it works, and it is much appreciated!!!