# if statement using "today"

#### ginstermanuk

##### New Member
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

### Excel Facts

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

#### ginstermanuk

##### New Member
Screenshot with data is attached

#### Attachments

• dashboard screenshot.jpg
152.9 KB · Views: 4

#### petertenthije

##### Board Regular
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
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
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

#### ginstermanuk

##### New Member
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!!!

Replies
2
Views
84
Replies
8
Views
118
Replies
7
Views
33
Replies
2
Views
65
Replies
1
Views
36