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):

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

<tbody>
</tbody>

Expected Result (Table2). The array formula mentioned above goes in column 'A':

	A	B
1	Bill	=TODAY()+30 (Current Result: 08/24/14)
2	John
3	Max
4
5
6
7
8
9
10

<tbody>
</tbody>

 

[somydelafomy]

I can upload a test document if needed.

 

[somydelafomy]

Any help would be greatly appreciated. Thanks in advance.