moose3415

Q: Change data displayed via Pop-up menu

I have a long list of data that needs to be narrowed down. Is there a formula/function that will change the data displayed as determined by a pop-up menu? For example:

 

Screen Shot 2016-05-05 at 10.33.08 AM.png

 

If I were to choose "Brown" from the above pop-up menu, only the entries whose hair is brown would be displayed (ie. Bilbo and Gimli); all others are hidden/not shown. To complicate matters, I have data in two different columns that I need to narrow the list by: in this case, the color of their hair, or the number of letters in their first name.

 

Using Numbers 3.6.1 in El Capitan. Thanks in advance for your help.

Posted on May 5, 2016 7:44 AM

Close

Q: Change data displayed via Pop-up menu

  • All replies
  • Helpful answers

  • by SGIII,Helpful

    SGIII SGIII May 7, 2016 1:55 PM in response to moose3415
    Level 6 (10,691 points)
    Mac OS X
    May 7, 2016 1:55 PM in response to moose3415

    If you're narrowing the list by more than one column have you considered using filters (in the panel at the right after clicking 'Sort & Filter' in the toolbar)?  You can add rules on more than one column at a time.

     

    SG

  • by t quinn,Apple recommended

    t quinn t quinn May 7, 2016 1:56 PM in response to moose3415
    Level 5 (5,041 points)
    Mac OS X
    May 7, 2016 1:56 PM in response to moose3415

    Hi moose,

     

    I agree with SG that filters are the way to go about this.

    Numbers has some built in functionality that may serve you directly. Click the little arrow in the column bar and filter table.

    Screen Shot 2016-05-05 at 2.54.59 PM.png

     

    If you want more, here is an example of just how deep you can go with this.

     

    Screen Shot 2016-05-05 at 2.58.42 PM.png

    Row two in this example is populated by popups. In column A choose a make,B a color, C year.

    The action all takes place in column D

    D3=IF(AND(OR(A3=A$2,A$2=""),OR($B$2="",$B$2=B3),OR($C$2="",$C$2=C3)),TRUE,FALSE)

    this is filled down.

    Screen Shot 2016-05-05 at 2.52.23 PM.png

    OR(A3=A$2,A$2="")

    OR($B$2="",$B$2=B3)

    OR($C$2="",$C$2=C3)

    These will return true if the row matches the header or is blank.

    AND() returns true if all the OR() statements are true.

     

    The filter is set to show all rows that show "TRUE" in D.

    Screen Shot 2016-05-05 at 2.45.09 PM.png

     

    As always I give credit to Badunit to opening my eyes to this approach.

     

    quinn

  • by moose3415,

    moose3415 moose3415 May 7, 2016 1:59 PM in response to moose3415
    Level 1 (12 points)
    Apple Music
    May 7, 2016 1:59 PM in response to moose3415

    Thanks for the suggestions guys- very helpful. I am still working on getting the formula just right but, as you both suggested I am using a formula to label the items that need to be displayed, then a filter to hide the ones I don't. Still learning AND and OR functions so it's been fun working it through. Thanks for the help.