Results 1 to 3 of 3

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Acolyte Weazel's Avatar
    Join Date
    Jul 2014
    Location
    Florida
    Posts
    26
    Articles
    0
    Excel Version
    2016
    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.

  3. #3
    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))),"")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •