+ Reply to Thread
Results 1 to 10 of 10

Selection of a column filter based on a cell value and filter that column automatically

  1. #1
    Registered User
    Join Date
    01-03-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    7

    Cool Selection of a column filter based on a cell value and filter that column automatically

    Hi,

    Please see attached small sample

    By selecting a diningtable A B C or D (the orange validated cell) I would like to filter the corresponding column A B C or D on value "x" so that the correct applicable products will be shown

    I would prefer not using code, and I am open to different set ups of the sheet to accomplish this in the most simple way

    Would anyone have a solution to this ?

    Thanks in advance,

    Kindest regards,

    Bart
    Holland
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Here is one solution.
    I'm guessing there is a better way if the overall structure changed, but perhaps this is good enough for your purposes.
    Attached Files Attached Files
    Please click the * icon below if I have helped.

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

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Try this...

    Enter this array formula** in I5:

    =IFERROR(INDEX(F:F,SMALL(IF(INDEX(A$5:E$10,,MATCH(G$1,A$4:E$4,0))="x",ROW(F$5:F$10)),ROWS(I$5:I5))),"")

    ** 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.

    Enter this formula in J5:

    =IF(I5="","",VLOOKUP(I5,F$5:G$10,2,0))

    Select I5:J5 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-03-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Thanks for your reply Tony, the solution looks like a feasible one, however, it does not work properly yet.

    In the attached sample you see the output of your formula in the table, below is how the output should look like, i.e. your formula for Dinertable A gives the output French Stick which is not containing an X.

    Next to that I woud require the rows with blank outcomes to disappear, like in filtering...

    Perhaps you have an alternative solution, otherwise many thanks for looking in to it.

    Kind Regards,
    Bart
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-03-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Thanks Melvin, this is a working solution.

    Is it possible to just use normal filtering based on the input cell Dinertable, i.e. when I select the Dinertable A that the focus will be on column A and that automatically will be filtered on column A rows containing an X ?

    Or would that always mean code ? And if so, would that be very basic code that I would understand with my very basic VBA skills ?

    Kind regards,
    Bart

  6. #6
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    If you want to filter the actual list, why not just highlight the table, select the filter option, and then go to the appropriate columns and filter A if you want A, etc...?

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

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Quote Originally Posted by b-a-r-t View Post
    Thanks for your reply Tony, the solution looks like a feasible one, however, it does not work properly yet.
    You didn't enter the formula in I5 as an array formula.

    Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

    When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-03-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Melvin, that is because the guys that have to use the list are not the smartest people of this world and don't know anything about Excel filters, I would like to accomplish that they select the Diningtable (actually it will be a work location) and that they automatically get the product list that belongs to that work location without any non-relevant information being displayed. Regards, Bart
    Last edited by b-a-r-t; 01-04-2013 at 06:05 AM.

  9. #9
    Registered User
    Join Date
    01-03-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    Thanks Tony, I actually had used the combination of keys but apparently not in the correct manner. Many thanks, I really appreciate your effort ! Cheers, Bart

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

    Re: Selection of a column filter based on a cell value and filter that column automaticall

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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