+ Reply to Thread
Results 1 to 9 of 9

FIltering items in a filter list using the IF function

  1. #1
    Registered User
    Join Date
    07-08-2016
    Location
    Coventry
    MS-Off Ver
    2013
    Posts
    5

    FIltering items in a filter list using the IF function

    Hello so I have multiple subjects in a column with information following in rows along the spreadsheet and have filtered the column so I can select a certain subject with all its information in the rows.

    What I would like to be able to do is in another cell make an IF function so that when a certain subject is filtered, it shows the information from the row I need in it but then when there is no filter applied, the information disappears.

    Hopefully this makes sense,

    Thanks!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FIltering items in a filter list using the IF function

    When you apply the filter will there ALWAYS only be one row of data visible?

    Can you post a SMALL sample file and show us what result you expect?

    20 rows and just a few columns worth of data is plenty.

    This is relatively easy but I need to see your data to offer the best solution.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-08-2016
    Location
    Coventry
    MS-Off Ver
    2013
    Posts
    5

    Re: FIltering items in a filter list using the IF function

    Hello there. Yes it will always only be one row of information when filtered hopefully the attached file explains. Thank you!
    Attached Files Attached Files

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FIltering items in a filter list using the IF function

    Ok, you have the yellow highlighted cell within the filter range. Depending on how you filter that means that row could be hidden and you won't see the formula result.

    So, I suggest you put the formula in cell G1 instead.

    This array formula** entered in G1:

    =IF(SUBTOTAL(3,A2:A15)=ROWS(A2:A15),"",INDEX(C2:C15,MATCH(1,SUBTOTAL(3,OFFSET(C2,,,ROW(C2:C15)-ROW(C2)+1)),0)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  5. #5
    Registered User
    Join Date
    07-08-2016
    Location
    Coventry
    MS-Off Ver
    2013
    Posts
    5

    Re: FIltering items in a filter list using the IF function

    Ye I didn't think about that when I was making the example. I need the formula to be outwith the filter range. That's great thanks very much!

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FIltering items in a filter list using the IF function

    You're welcome. Thanks for the feedback!

  7. #7
    Registered User
    Join Date
    07-08-2016
    Location
    Coventry
    MS-Off Ver
    2013
    Posts
    5

    Re: FIltering items in a filter list using the IF function

    Sorry to bring this up again but only getting round to using this formula now. Do I have to change the formula to display dates? I cannot get it to work. It is displaying a number which is not the date in the box needed. Hopefully you can help!

  8. #8
    Registered User
    Join Date
    07-08-2016
    Location
    Coventry
    MS-Off Ver
    2013
    Posts
    5

    Re: FIltering items in a filter list using the IF function

    Just figured it out. Sorry to bother you again!

  9. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: FIltering items in a filter list using the IF function

    Good deal!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] filter pivot table for items in a list in another workbook if found
    By simarui in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-23-2015, 05:35 PM
  2. [SOLVED] Filtering Arrays using the filter function
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-27-2014, 12:47 PM
  3. LOOKUP function in creation of filtering list of employees
    By megaiooo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-09-2013, 12:52 AM
  4. VBA to Filter Items Not in Given List (3rd Filter Criterion)
    By jasoncw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-11-2013, 12:35 PM
  5. [SOLVED] Help with list filtering function
    By imperitor in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2012, 02:20 PM
  6. Replies: 3
    Last Post: 05-15-2011, 11:46 AM
  7. Filter Combo Box list items based on partially entered text
    By Balsams in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-12-2009, 12:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1