FIltering items in a filter list using the IF function

**NedShneeb** · 07-08-2016, 10:21 AM

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!

**Tony Valko** · 07-08-2016, 11:22 AM

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.

**NedShneeb** · 07-08-2016, 12:23 PM

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

**Tony Valko** · 07-08-2016, 12:42 PM

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.

**NedShneeb** · 07-08-2016, 12:55 PM

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!

**Tony Valko** · 07-08-2016, 01:05 PM

You're welcome. Thanks for the feedback!

**NedShneeb** · 07-11-2016, 02:55 AM

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!

**NedShneeb** · 07-11-2016, 03:26 AM

Just figured it out. Sorry to bother you again!

**Tony Valko** · 07-11-2016, 04:47 AM

Good deal!

FIltering items in a filter list using the IF function

LinkBack

Thread Tools

Rate This Thread

Display

FIltering items in a filter list using the IF function

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] filter pivot table for items in a list in another workbook if found

[SOLVED] Filtering Arrays using the filter function

LOOKUP function in creation of filtering list of employees

VBA to Filter Items Not in Given List (3rd Filter Criterion)

[SOLVED] Help with list filtering function

Filtering Data Validation List Without Using Advanced Filter

Filter Combo Box list items based on partially entered text

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions