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
|
|
|
Bookmarks