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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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,118,765
Messages
5,574,111
Members
412,571
Latest member
Ventura7
Top