fatekeeper
New Member
- Joined
- Nov 12, 2021
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
I am trying to create an excel file that will allow order information to be entered and then to allow for the status of each line to be tracked and also to have color coding to flag orders depending on the ship date.
I created a worksheet that uses a drop down box (pulls from a list on a different worksheet in the same workbook) so the user can select different statuses, and I applied conditional formatting to change the color of each line slightly so the users know at a glance which orders are in which status. My problem comes when trying to apply conditional formatting to cells in a column that displays the estimated ship date. I want the cell with the ship date and to turn green fill for orders shipping within the next week (7 days) to help flag them for follow-up and red fill if the date has already passed, but not to change if the status column indicates it has already shipped.
I have the conditional formatting rule for Shipped status as the first one have it marked to stop if that rule is triggered, which is working as expected. My issue is that now when any other status is selected, if there is not yet a date in the ship date field, it remains red. I would like it to stay the same color as the status would indicate UNLESS it's a date in the next 7 days (fill green) or a past date but not yet shipped (fill red) - is there a way to do this?
I created a worksheet that uses a drop down box (pulls from a list on a different worksheet in the same workbook) so the user can select different statuses, and I applied conditional formatting to change the color of each line slightly so the users know at a glance which orders are in which status. My problem comes when trying to apply conditional formatting to cells in a column that displays the estimated ship date. I want the cell with the ship date and to turn green fill for orders shipping within the next week (7 days) to help flag them for follow-up and red fill if the date has already passed, but not to change if the status column indicates it has already shipped.
I have the conditional formatting rule for Shipped status as the first one have it marked to stop if that rule is triggered, which is working as expected. My issue is that now when any other status is selected, if there is not yet a date in the ship date field, it remains red. I would like it to stay the same color as the status would indicate UNLESS it's a date in the next 7 days (fill green) or a past date but not yet shipped (fill red) - is there a way to do this?
Enersys Order Tracker.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | Date Submitted | Customer | PO# | MATERIAL | Quote# | ESD | Notes | Status | ||
2 | 11/1/2021 | 1234 | SAMPLE1 | QN2021-08 | 12/30/2021 | Shipped | ||||
3 | 12/20/2021 | 5678 | SAMPLE2 | QN2022-02 | 12/31/2021 | OA Received | ||||
4 | 1/3/2022 | 2468 | SAMPLE3 | QN2022-01 | Submitted | |||||
5 | ||||||||||
Open Orders |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F3:F5 | Expression | =$H3="Shipped" | text | YES |
F3:F5 | Expression | =AND(F3>TODAY(),F3<=(TODAY()+7)) | text | NO |
F3:F5 | Expression | =$F3=(TODAY()-$F$2)>=1 | text | NO |
F3:F5 | Expression | =$H3="OA Received" | text | NO |
F3:F5 | Expression | =$H3="Submitted" | text | NO |
A2:G2 | Expression | =$H2="Shipped" | text | YES |
F2 | Expression | =AND(F2>TODAY(),F2<=(TODAY()+7)) | text | NO |
H3:H2000 | Cell Value | =Lists!$A$4 | text | NO |
H3:H2000 | Cell Value | =Lists!$A$3 | text | NO |
H3:H2000 | Cell Value | =Lists!$A$2 | text | NO |
A6:G2050,A5:E5,G5 | Expression | =$H5="Shipped" | text | NO |
A6:G2050,A5:E5,G5 | Expression | =$H5="OA Received" | text | NO |
A6:G2050,A5:E5,G5 | Expression | =$H5="Submitted" | text | NO |
A3:E4,G3:G4 | Expression | =$H3="Shipped" | text | NO |
A3:E4,G3:G4 | Expression | =$H3="OA Received" | text | NO |
A3:E4,G3:G4 | Expression | =$H3="Submitted" | text | NO |
F2 | Expression | =$F2=(TODAY()-$F$2)>=1 | text | NO |
A2:G2 | Expression | =$H2="OA Received" | text | NO |
A2:G2 | Expression | =$H2="Submitted" | text | NO |
H2 | Cell Value | =Lists!$A$4 | text | NO |
H2 | Cell Value | =Lists!$A$3 | text | NO |
H2 | Cell Value | =Lists!$A$2 | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
H2:H4 | List | =Lists!$A:$A |
H5 | List | =Lists!$A:$A |