curiouscoder
New Member
- Joined
- Jun 24, 2022
- Messages
- 31
- Office Version
- 365
- Platform
- Windows
my formula in each status column
$A$8 is where the "assigned" is being grabbed from in another sheet, so $A$9 would be "in progress" etc.
status is drop down list of options, assigned, in progress and everything after that are the statuses
here is a small snippet of my table, there are more statuses after "in progress" but have not been included
the XLOOKUP is looking up the values and returning them from this table:
so the table above is a changelog that automatically records the values when a status is changed in table 1 and automatically records it on here with the details provided above.
the xlookup then refers to this table by grabbing the Customer ID and status column that matchup in table 1 and returns the date and time in table 1 under each status column.
my question is when for example from table 1, customer ID, J2 has 2 timestamps. when i delete the status in the drop down to start again all the entries for the dates with that customer ID disappear (I want this)
but if I click on the first status "assigned" for J2 again I want it to only show me the timestamp for "assigned", yet it returns all previous records, thus populating "assigned" and "in progress".
i know it would update one at a time like i wanted if i cleared the changelog but I want to keep it.
is there a way I can amend my formula with something like an IF statement to get it how I want?
thank you
Excel Formula:
=IF([@Status]="","",IFERROR(XLOOKUP(F2&'DROPDOWN CATS'!$A$8,Table2[Customer ID]&Table2[New Status],Table2[Date + Time],,,-1),""))$A$8 is where the "assigned" is being grabbed from in another sheet, so $A$9 would be "in progress" etc.
status is drop down list of options, assigned, in progress and everything after that are the statuses
here is a small snippet of my table, there are more statuses after "in progress" but have not been included
| Alert ID | Customer ID | Entity Type | Status | Pod (Team) | Reviewer | Assigned | In Progress |
| J1 | |||||||
| J2 | 1 - Assigned | 01/07/2022 10:24:43 | 01/07/2022 09:53:41 | ||||
| J3 | 1 - Assigned | 01/07/2022 10:19:57 | |||||
| J4 | 1 - Assigned | 01/07/2022 10:24:52 | |||||
| J5 | 1 - Assigned | 01/07/2022 10:15:31 | |||||
| J6 | 2 - In Progress | 01/07/2022 10:27:08 | 01/07/2022 10:27:16 | ||||
| J7 | |||||||
| J8 | 2 - In Progress | 01/07/2022 10:27:21 | 01/07/2022 10:27:24 |
the XLOOKUP is looking up the values and returning them from this table:
| Date + Time | Cell Changed | Old Status | New Status | Customer ID | Date | Time |
01/07/2022 09:50 | $H$3 | 1 - Assigned | J2 | 01/07/2022 | 09:50 | |
01/07/2022 09:53 | $H$3 | 2 - In Progress | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:53 | $H$3 | 3 - Escalated Pre CC | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:53 | $H$3 | 4 - Sent for Initial Check | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:53 | $H$3 | 5 - Returned from Initial Check | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:53 | $H$3 | 6 - Sent for CC | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:53 | $H$3 | 7 – Request for more info from CC | J2 | 01/07/2022 | 09:53 | |
01/07/2022 09:54 | $H$29 | 1 - Assigned | J76 | 01/07/2022 | 09:54 | |
01/07/2022 10:07 | $H$3 | 7 – Request for more info from CC | 1 - Assigned | J2 | 01/07/2022 | 10:07 |
01/07/2022 10:13 | $H$2 | 1 - Assigned | 2 - In Progress | J1 | 01/07/2022 | 10:13 |
01/07/2022 10:13 | $H$2 | 1 - Assigned | 3 - Escalated Pre CC | J1 | 01/07/2022 | 10:13 |
01/07/2022 10:14 | $H$28 | 1 - Assigned | 1 - Assigned | J27 | 01/07/2022 | 10:14 |
01/07/2022 10:14 | $H$2 | 3 - Escalated Pre CC | 2 - In Progress | J1 | 01/07/2022 | 10:14 |
01/07/2022 10:14 | $H$2 | 2 - In Progress | 1 - Assigned | J1 | 01/07/2022 | 10:14 |
01/07/2022 10:15 | $H$6 | 1 - Assigned | J5 | 01/07/2022 | 10:15 | |
01/07/2022 10:15 | $H$29 | 1 - Assigned | J76 | 01/07/2022 | 10:15 | |
01/07/2022 10:16 | $H$29 | 1 - Assigned | 1 - Assigned | J76 | 01/07/2022 | 10:16 |
01/07/2022 10:16 | $H$29 | 1 - Assigned | 1 - Assigned | J76 | 01/07/2022 | 10:16 |
01/07/2022 10:16 | $H$29 | 1 - Assigned | 1 - Assigned | J76 | 01/07/2022 | 10:16 |
01/07/2022 10:19 | $H$4 | 1 - Assigned | J3 | 01/07/2022 | 10:19 | |
01/07/2022 10:24 | $H$3 | 1 - Assigned | J2 | 01/07/2022 | 10:24 | |
01/07/2022 10:24 | $H$5 | 1 - Assigned | J4 | 01/07/2022 | 10:24 | |
01/07/2022 10:25 | $H$18 | 1 - Assigned | J17 | 01/07/2022 | 10:25 | |
01/07/2022 10:26 | $H$2 | 1 - Assigned | J1 | 01/07/2022 | 10:26 | |
01/07/2022 10:27 | $H$7 | 1 - Assigned | J6 | 01/07/2022 | 10:27 | |
01/07/2022 10:27 | $H$7 | 2 - In Progress | J6 | 01/07/2022 | 10:27 | |
01/07/2022 10:27 | $H$9 | 1 - Assigned | J8 | 01/07/2022 | 10:27 | |
01/07/2022 10:27 | $H$9 | 2 - In Progress | J8 | 01/07/2022 | 10:27 | |
01/07/2022 10:27 | $H$10 | 1 - Assigned | J9 | 01/07/2022 | 10:27 | |
01/07/2022 10:27 | $H$10 | 2 - In Progress | J9 | 01/07/2022 | 10:27 | |
01/07/2022 10:30 | $H$2 | 1 - Assigned | 2 - In Progress | J1 | 01/07/2022 | 10:30 |
01/07/2022 10:31 | $H$2 | 1 - Assigned | 2 - In Progress | J1 | 01/07/2022 | 10:31 |
01/07/2022 10:33 | $H$2 | 2 - In Progress | 1 - Assigned | J1 | 01/07/2022 | 10:33 |
01/07/2022 10:34 | $H$11 | 1 - Assigned | J10 | 01/07/2022 | 10:34 | |
01/07/2022 10:34 | $H$11 | 2 - In Progress | J10 | 01/07/2022 | 10:34 | |
01/07/2022 10:34 | $H$11 | 3 - Escalated Pre CC | J10 | 01/07/2022 | 10:34 | |
01/07/2022 10:34 | $H$11 | 1 - Assigned | J10 | 01/07/2022 | 10:34 | |
01/07/2022 10:34 | $H$11 | 1 - Assigned | J10 | 01/07/2022 | 10:34 | |
01/07/2022 10:35 | $H$11 | 2 - In Progress | J10 | 01/07/2022 | 10:35 | |
01/07/2022 10:35 | $H$11 | 3 - Escalated Pre CC | J10 | 01/07/2022 | 10:35 |
so the table above is a changelog that automatically records the values when a status is changed in table 1 and automatically records it on here with the details provided above.
the xlookup then refers to this table by grabbing the Customer ID and status column that matchup in table 1 and returns the date and time in table 1 under each status column.
my question is when for example from table 1, customer ID, J2 has 2 timestamps. when i delete the status in the drop down to start again all the entries for the dates with that customer ID disappear (I want this)
but if I click on the first status "assigned" for J2 again I want it to only show me the timestamp for "assigned", yet it returns all previous records, thus populating "assigned" and "in progress".
i know it would update one at a time like i wanted if i cleared the changelog but I want to keep it.
is there a way I can amend my formula with something like an IF statement to get it how I want?
thank you
