Change data displayed via Pop-up menu

Question

Level 1

14 points

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:

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

Reply

Answer 1

Best reply

SGIII

Level 8

35,324 points

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

Reply

Answer 2

t quinn

Level 5

7,043 points

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.

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

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.

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.

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

quinn

Reply

Answer 3

moose3415 Author

Level 1

14 points

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.

Reply