alexanders
New Member
- Joined
- Jan 29, 2014
- Messages
- 29
- Office Version
- 365
- Platform
- Windows
Hello I have several sheets like this:
And I would like a status sheet able to automatically locate the overdue items and display them something like this:
But whenever I try it picks up the first overdue item but then just repeats it over and over
Is there a way to lookup over multiple sheets and display the items which are overdue please?
CIP Workbook 2022.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | 03/01/2022 | ||||||||||
2 | Week 1 | ||||||||||
3 | Plant Item | Frequency | Date of Last Event | Days Since Event | Days Until Action Req'd | Clean Type | Name | Date | |||
4 | overdue | Pasteuriser Sample Tap Membrane | Weekly | 01/11/2021 | 20 | 13 days overdue! | Hot Caustic | AB | 01/11/2021 | ||
5 | Post passi Media Sample (sterile sample) | Weekly | 17/11/2021 | 4 | 3 | Cold Caustic | AC | 17/11/2021 | |||
6 | Soak baths | Weekly | 18/11/2021 | 3 | 4 | Hot Rinse | AA | 18/11/2021 | |||
7 | Pasteuriser strainers | Weekly | 19/11/2021 | 2 | 5 | Cold Rinse | AC | 19/11/2021 | |||
8 | overdue | Juice Buffer Tank | Weekly | 01/11/2021 | 20 | 13 days overdue! | Hot Caustic | AA | 01/11/2021 | ||
9 | Media Blender and Passi Main (New) | Weekly | 17/11/2021 | 4 | 3 | Hot Caustic | AC | 17/11/2021 | |||
10 | Media Blender and Passi Main (Old) | Weekly | 18/11/2021 | 3 | 4 | Cold Caustic | AA | 18/11/2021 | |||
11 | FFRT Lees Main | Weekly | 19/11/2021 | 2 | 5 | Hot Rinse | AC | 19/11/2021 | |||
12 | overdue | Transfer Main 1A | Weekly | 01/11/2021 | 20 | 13 days overdue! | Cold Rinse | AA | 01/11/2021 | ||
13 | Transfer Main 2A | Weekly | 16/11/2021 | 5 | 2 | Hot Caustic | AC | 16/11/2021 | |||
14 | Transfer Main 1B | Weekly | 17/11/2021 | 4 | 3 | Hot Caustic | AA | 17/11/2021 | |||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D4:D14 | D4 | =IF(MAX(G4:FF4)=0/1/1900,"NOT DONE",MAX(G4:FF4)) |
E4:E14 | E4 | =IF(MAX(G4:FF4)=0/1/1900,"",(TODAY()-MAX(G4:I4))) |
F4:F14 | F4 | =IF(E4="","",IF(E4<=7,7-E4,IF(AND(E4>7,E4<=14),"Action within "&14-E4&" days",IF(E4>14,E4-7&" days overdue!")))) |
A4:A14 | A4 | =IFERROR(IF(SEARCH("overdue",F4),"overdue"),"") |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
G4:G25 | List | =Data!$A$2:$A$14 |
H4:H25 | List | =Data!$B$2:$B$35 |
I4:I25 | Custom | =I4=TODAY() |
And I would like a status sheet able to automatically locate the overdue items and display them something like this:
CIP Workbook 2022.xlsx | |||||||
---|---|---|---|---|---|---|---|
A | B | C | D | E | |||
1 | Overdue Cleans Status Report | ||||||
2 | Plant Item | Frequency | Date of Last Event | Days since last event | Days until next action required | ||
3 | Pasteuriser Sample Tap Membrane | Weekly | 01/11/2021 | 20 | 13 days overdue! | ||
4 | Juice Buffer Tank | Weekly | 01/11/2021 | 20 | 13 days overdue! | ||
5 | Transfer Main 1A | Weekly | 01/11/2021 | 20 | 13 days overdue! | ||
6 | |||||||
7 | |||||||
Status |
But whenever I try it picks up the first overdue item but then just repeats it over and over
Is there a way to lookup over multiple sheets and display the items which are overdue please?