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:
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
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
Posted on May 7, 2016 1:56 PM




