willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 929
- Office Version
- 365
- Platform
- Windows
I am looking for help with these 2 formulas:
=DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]]))
=IFERROR(@INDEX($E$2:E74,AGGREGATE(14,6,(ROW($B$2:B74)-ROW($B$2)+1)/($B$2:B108=B74),2)),"")
I know the spreadsheet is messy at the moment as many people have been playing around in it, however what these 2 formulas do is find the previous audit completion date and return the next schedule date. However these formulas don't know how to recognize if the previous audit date is blank or if that particular audit was never done before to begin with.
What I am looking for is instead of returning "October-1900" for if there is a blank cell for the previous audit for the formula to return "Previous Audit Incomplete"
If it cannot find it at all the same message is fine as well
Thank you to anyone who can help!
Note: Ignore A72 that was me playing around...
=DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]]))
=IFERROR(@INDEX($E$2:E74,AGGREGATE(14,6,(ROW($B$2:B74)-ROW($B$2)+1)/($B$2:B108=B74),2)),"")
I know the spreadsheet is messy at the moment as many people have been playing around in it, however what these 2 formulas do is find the previous audit completion date and return the next schedule date. However these formulas don't know how to recognize if the previous audit date is blank or if that particular audit was never done before to begin with.
What I am looking for is instead of returning "October-1900" for if there is a blank cell for the previous audit for the formula to return "Previous Audit Incomplete"
If it cannot find it at all the same message is fine as well
Thank you to anyone who can help!
Company Internal Audit Schedule.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
54 | June-2022 | Process Audit 6 - | 1 | Employee 4 | 27-Aug-21 | ||||
55 | July-2022 | Process Audit 6 - | 1 | Employee 2 | 30-Sep-22 | Y | 19-Sep-21 | ||
56 | July-2022 | Process Audit 4 - Group | 1 | Employee 2 | 26-Sep-22 | Y | 30-Sep-21 | ||
57 | July-2022 | Process Audit 6 - (CA) | 1 | Employee 5 | 30-Sep-22 | Y | 30-Sep-21 | ||
58 | August-2022 | Process Audit 2 - | 1 | Employee 4 | 29-Oct-21 | ||||
59 | September-2022 | Process Audit 1 - | 1 | Employee 4 | 28-Nov-21 | ||||
60 | October-2022 | Process Audit 1 - (CA) | 1 | Employee 5 | 25-Nov-22 | Y | 15-Dec-21 | ||
61 | October-2022 | Process Audit 1 - | 1 | Employee 2 | 30-Nov-22 | N | 21-Jul-21 | ||
62 | March-2023 | Process Audit 2 - (US) | 3 | Employee 1 | 16-May-22 | ||||
63 | March-2023 | Process Audit 4 - (US) | 3 | Employee 1 | 17-May-22 | ||||
64 | March-2023 | Process Audit 5 - (US) | 3 | Employee 1 | 18-May-22 | ||||
65 | March-2023 | Process Audit 6 - (US) | 3 | Employee 1 | 18-May-22 | ||||
66 | June-2023 | Process Audit 3 - Group | 1 | Employee 2 | 12-Aug-22 | ||||
67 | July-2023 | Process Audit 4 - Group | 1 | Employee 3 | 26-Sep-22 | ||||
68 | July-2023 | Process Audit 6 - | 1 | Employee 4 | 30-Sep-22 | ||||
69 | July-2023 | Process Audit 6 - (CA) | 1 | Employee 2 | 30-Sep-22 | ||||
70 | August-2023 | Process Audit 2 - (CA) | 1 | Employee 6 | 25-Oct-22 | ||||
71 | September-2023 | Process Audit 1 - (CA) | 1 | Employee 3 | 25-Nov-22 | ||||
72 | October-1900 | Process Audit 1 - | 1 | 0-Jan-00 | |||||
73 | September-2023 | Process Audit 1 - | 1 | Employee 5 | 30-Nov-22 | ||||
74 | October-1900 | Process Audit 2 - | 1 | 0-Jan-00 | |||||
75 | September-2023 | Process Audit 2 - | 1 | Employee 5 | 30-Nov-22 | ||||
76 | October-1900 | Process Audit 6 - | 1 | 0-Jan-00 | |||||
Division Schedule |
Cell Formulas | ||
---|---|---|
Range | Formula | |
G54,G61 | G54 | =IFERROR(@INDEX($E$2:E54,AGGREGATE(14,6,(ROW($B$2:B54)-ROW($B$2)+1)/($B$2:B54=B54),2)),"") |
G55 | G55 | =IFERROR(INDEX($E$2:E55,AGGREGATE(14,6,(ROW($B$2:B55)-ROW($B$2)+1)/($B$2:B91=B55),2)),"") |
G56 | G56 | =IFERROR(INDEX($E$2:E56,AGGREGATE(14,6,(ROW($B$2:B56)-ROW($B$2)+1)/($B$2:B88=B56),2)),"") |
G57,G62:G76 | G57 | =IFERROR(INDEX($E$2:E57,AGGREGATE(14,6,(ROW($B$2:B57)-ROW($B$2)+1)/($B$2:B91=B57),2)),"") |
A54:A60,A62:A71,A73:A76 | A54 | =DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]])) |
A72 | A72 | =IFERROR(DATE(YEAR([@[Previous Audit Date]])+1,MONTH([@[Previous Audit Date]])-2,DAY([@[Previous Audit Date]])),"Previous Audit Date not found") |
G59 | G59 | =IFERROR(@INDEX($E$2:E59,AGGREGATE(14,6,(ROW($B$2:B59)-ROW($B$2)+1)/($B$2:B65=B59),2)),"") |
G60 | G60 | =IFERROR(INDEX($E$2:E60,AGGREGATE(14,6,(ROW($B$2:B60)-ROW($B$2)+1)/($B$2:B93=B60),2)),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80 | Expression | =$P2="Past Due" | text | NO |
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80 | Expression | =$L2="Y" | text | NO |
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80 | Expression | =AND($F2="N",$E2<>"") | text | NO |
A2:P2,A81:P4985,D62:I80,A3:I61,A62:A80,J3:P80 | Expression | =$F2="Y" | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B62:B1048576 | List | ='Audit List'!$I$2:$I$18 |
Note: Ignore A72 that was me playing around...