if statement using "today"

ginstermanuk

New Member
Joined
Sep 5, 2012
Messages
8
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

ginstermanuk

New Member
Joined
Sep 5, 2012
Messages
8
Screenshot with data is attached
 

Attachments

  • dashboard screenshot.jpg
    dashboard screenshot.jpg
    152.9 KB · Views: 4

petertenthije

Board Regular
Joined
Sep 25, 2012
Messages
224
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

New Member
Joined
Sep 5, 2012
Messages
8
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

Board Regular
Joined
Sep 25, 2012
Messages
224
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

New Member
Joined
Sep 5, 2012
Messages
8
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!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,897
Members
412,752
Latest member
LUIS SAMANO
Top