Name Listing if date is expired and if visible (Filtered) - Tricky Formula

[somydelafomy]

Good day dear Excel Gurus.

I've been working on a training monitoring table for around 500 employee with the ultimate purpose to monitor around 15 training dates per employee. For forum purposes, I have simplified the table but the structure remains the same on the full scale.

In the example below (Table 1) I use a filters in the trade column(And others column in my main document) so we can reschedule the training per trade and other filters. In the column 'A' I use a logical test that returns 1 if the row is visible and return 0 if the row is filtered out. In this example, the employee working for the Shipping should be filtered out and not visible but for the demonstration, I left them there.

My goal (In table 2) is to produce a list per training which would return the name of a person if his training has expired or is set to expire in 1 month. The reference date that I use for the listing needs to use the following format =Today()+30. The list needs to exclude the names that appear on hidden rows (Filtered out).

I came up with the following Array formula that goes in column 'A' in table 2:

{=IF(ISERROR(INDEX(TABLE1!$A$2:$E$10,SMALL(IF(TABLE1!$E$2:$E$10<$B$1,ROW(TABLE1!$E$2:$E$10)),ROW(1:1)),2)),"",INDEX(TABLE1!$A$2:$E$10,SMALL(IF(TABLE1!$A$2:$A$10=1,IF(TABLE1!$E$2:$E$10<$B$1,ROW(TABLE1!$E$2:$E$10)))),ROW(1:1),2))}

I know I'm close to the expected result as I got this formula working in a simplified table without the filter condition. But I've been hammering this issue since a while and I figured I would ask here as my excel knowledge is somewhat limited(Not quite a Guru yet!) and since this might be a very simple thing do for advanced Excel users.

Additionally, I'm using Excel 2010 and I would like to keep my workbook macro-free if possible.

Thanks in advance for your support.

Spreadsheet (Table1):

[TABLE="class: grid, width: 600, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]=SubTotal(102, A1)
(Returns 1 if row is visible and 0 if hidden based on the applied filters)[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"]Employee #[/TD]
[TD="align: center"]Trade
(Shipping is filtered out)[/TD]
[TD="align: center"]Training[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Bill[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]Sales[/TD]
[TD="align: center"]07/10/14
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Joe[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]Admin[/TD]
[TD="align: center"]09/12/14[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Peter
[/TD]
[TD="align: center"]117[/TD]
[TD="align: center"]Shipping
[/TD]
[TD="align: center"]04/17/14
[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]John
[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]HR[/TD]
[TD="align: center"]02/24/13
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]224[/TD]
[TD="align: center"]HR[/TD]
[TD="align: center"]12/25/14[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]Admin[/TD]
[TD="align: center"]5/23/14
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Isa[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]Shipping
[/TD]
[TD="align: center"]09/17/14[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]Marry[/TD]
[TD="align: center"]149[/TD]
[TD="align: center"]Shipping
[/TD]
[TD="align: center"]10/04/15[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Lois[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]IT[/TD]
[TD="align: center"]12/17/14[/TD]
[/TR]
</tbody>[/TABLE]




















Expected Result (Table2). The array formula mentioned above goes in column 'A':
[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]Bill
[/TD]
[TD="align: center"]=TODAY()+30 (Current Result: 08/24/14)[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]John
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]Max
[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[somydelafomy]

I can upload a test document if needed.

 

[somydelafomy]

Any help would be greatly appreciated. Thanks in advance.