Filter Index Formula - How to Exclude Value from List

[jessitarexcel]

Hello,

May I ask for some help resolving this query please? I seem to be making an error when I am trying to exclude one field if there is data entered. I will explain further below.

I am looking at some data with a series of dates. The dates are used to determine if the submission should appear in our table or not. There are a number of conditions:

Column: BA Column Header: ITG SAG On Hold Follow Up Date	Column: BB Column Header: Last Date contacted
2/09/2022	25/08/2022
5/09/2022	20/08/2022
	18/08/2022

And the Days Since Last Contact Column:

Column: BM Column Header: Days Since Last Contact

5

10

12

The current conditions are as follows:

If the application has equal to or more than 14 Days Since Last Contact and Days Since Last Contact is blank and the ITG SAG On Hold Follow Up Column date is greater than today, and if those same conditions and the ITG SAG On Hold is blank, then return No Data. This filter returns a list of applications that have had the longest time elapse since their last Last Contact Date and don't have SAG On Hold Follow Up Date in the future.

The sample table where the data is filtered is below:

Column & Row: K61 Column Header: Submission No.

Column & Row: L61 Column Header: Applicant Info (Name, Address, Phone No and Email combined into a single column using textjoin)

Column & Row: M61 Column Header: Days Since Last Contact (Formula that takes the last contact date from today's date to get the time since last contact)

This is my original filter and index formula:

=FILTER(INDEX(TableTracker_DNC,SEQUENCE(ROWS(TableTracker_DNC)),XMATCH(K61:M61,TableTracker_DNC[#Headers])),(TableTracker_DNC[Days Since Last Contact]>=14)*(TableTracker_DNC[Days Since Last Contact]<>"")*(TableTracker_DNC[ITG SAG On Hold Follow Up Date]<TODAY()) + (TableTracker_DNC[Days Since Last Contact]>=14)*(TableTracker_DNC[Days Since Last Contact]<>"")*(TableTracker_DNC[ITG SAG On Hold Follow Up Date]=""),"No Data")

And the formula filters into this table:

This is my new formula, however, when I enter a date that is equal to or less than today's date, the application still appears in the list:

=FILTER(INDEX(TableTracker_DNC,SEQUENCE(ROWS(TableTracker_DNC)),XMATCH(K61:M61,TableTracker_DNC[#Headers])),(TableTracker_DNC[Days Since Last Contact]>=14)*(TableTracker_DNC[Days Since Last Contact]<>"")*(TableTracker_DNC[ITG SAG On Hold Follow Up Date]<TODAY()) + (TableTracker_DNC[Days Since Last Contact]>=14)*(TableTracker_DNC[Days Since Last Contact]<>"")*(TableTracker_DNC[ITG SAG On Hold Follow Up Date]="")*(TableTracker_DNC[Date CR Notified works completed]>=TODAY()),"No Data")

The requested changes to the conditions:

We would like to return the applications with the same original conditions, however, we would like to remove any applications from that list that have a date appear in the column with header title: Date CR Notified works completed. Therefore, this isn't a condition to include, we want to exclude any applications that have a date entered into that column. Can someone please assist with how I might be able to add in the condition to exclude? I know I will most likely need to try another formula. I am a little stumped. Do I add in an IF condition to the formula - as in IF there is a date entered into the CR Notified Works Completed column, exclude from the list.

Or this a matter of, we need to enter a date in the last date contacted that is way into the future? That is what we do now but I figured this could most likely be solved by adjusting this formula or by adding another condition to it. I know this isn't difficult but I am getting stuck. Any help would be sincerely appreciated.

I have a sheet I can attach or send through that someone can use if that helps.

 

[jessitarexcel]

I cannot see a way to attach a file to the post.

 

[jessitarexcel]

I have managed to resolve this issue and will post the solution shortly.