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 expiration 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, A2) (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

Expected Result (Table2). The array formula mentioned above goes in cell 'A1' and then it needs to be expend down:

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

[Weazel]

It looks like the second part of your formula is very close though I would write it a little differently but that could just be personal preference.

since you are using excel 2010 you could replace the iserror handling part of the formula with IFERROR which would shorten the formula up a little.

On sheet 2 in B1 I have the date today()+30.

so E2:E10 less than 8/24/2014, from your example.

so in A1 on sheet2 you can enter....

=IFERROR(INDEX(Table1!$B$2:$B$10,SMALL(IF(Table1!$E$2:$E$10<$B$1,IF(Table1!$A$2:$A$10=1,ROW(Table1!$A$2:$A$10)-ROW(Table1!$A$2)+1)),ROWS($A$1:A1))),"") Control Shift Enter

and drag it down until you get blanks.

[Bob Phillips]

I read that you don't want to show a name if it is filtered out, so try

=IFERROR(INDEX(TABLE1!$B$2:$B$10,SMALL(IF((SUBTOTAL(3,OFFSET(TABLE1!$B$1,ROW(TABLE1!$B$2:$B$10)-ROW(TABLE1!$A$1),,1)))*(TABLE1!$E$2:$E$10<$B$1)*(TABLE1!$A$2:$A$10=1),ROW(TABLE1!$A$2:$A$10)-ROW(TABLE1!$A$2)+1),ROW($A1))),"")