somydelafomy
New member
- Joined
- Jul 29, 2014
- Messages
- 1
- Reaction score
- 0
- Points
- 0
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):
Expected Result (Table2). The array formula mentioned above goes in cell 'A1' and then it needs to be expend down:
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 |
