User profile for user: Mr-Styx
Mr-Styx Author
User level: Level 2
155 points

Numbers - Text dropdown conditional selection.

Hi everyone,


I am trying to figure out if I can make multiple lists in pages and allow only specific options in the second list to show up.


For example :

cell A1 has a list of { dog, cat, lizard },

cell B1 has a list of ( Shepard, tabby, gecko ).


how do I get cell B1 to show only "tabby" if cat is in cell A1 or only "Shepard" if dog is selected in cell A1.


I plan to put all the lists on a separate page but I don't know how to get the conditional logic to work for the first page. right now its just stuck showing the entire list for cell B1 no matter what's selected in cell A1


Any suggestions would be greatly appreciated!



Posted on Aug 16, 2021 9:38 PM

Reply
Question marked as Top-ranking reply
User profile for user: Mr-Styx
Mr-Styx Author
User level: Level 2
155 points

Posted on Aug 20, 2021 11:06 AM

Hi Barry +.everyone that responded,


Thank you so much for the assistance and insight. I think what I originally was asking for was in fact cascading menus. Unfortunately by the responses I don't think that this will be possible until numbers brings support for this information.


But this option really helped for at least cutting down the time and gave me some idea's as well.


I was also able to incorporate everyones help with https://discussions.apple.com/docs/DOC-250004071


I found some what of a work around by building the sheet the way I was hoping to view it and used cell A1(could have added a single cell table on the side with the drop down to use as the checker for column C) to create a dropdown of the animal basic types {cat, dog, lizard}.

here's the list for A1


here's the list for B2 - cat row



I setup an if statement to check to see what A1 was listed as in the C column for each main type. This was put into Cell C2 for the cat row


repeated this step for each animal row but changed "Cat" to the other animals dog and lizard.

  • as a side note I had to place 1 in the C1 column so that the drop down checker in A1 always appears later on.



once this is set only the drop down for the animal selected in Cell A1.

I hid column C so that it wasn't annoying me and the filter still worked.

( Like I mentioned at the beginning of the response it may be better to add a single cell table above or to the left of the table so that the its more user friendly. )



Thanks again for everyones help I hope that dynamic drop downs will eventually be implemented to make this much easier. or if a function would output a drop down so that you could import different data to the drop down from the steps above would be nice as well.


Y'all are awesome!









View in context

Similar questions

8 replies
Question marked as Top-ranking reply
User profile for user: Mr-Styx
Mr-Styx Author
User level: Level 2
155 points

Aug 20, 2021 11:06 AM in response to Barry

Hi Barry +.everyone that responded,


Thank you so much for the assistance and insight. I think what I originally was asking for was in fact cascading menus. Unfortunately by the responses I don't think that this will be possible until numbers brings support for this information.


But this option really helped for at least cutting down the time and gave me some idea's as well.


I was also able to incorporate everyones help with https://discussions.apple.com/docs/DOC-250004071


I found some what of a work around by building the sheet the way I was hoping to view it and used cell A1(could have added a single cell table on the side with the drop down to use as the checker for column C) to create a dropdown of the animal basic types {cat, dog, lizard}.

here's the list for A1


here's the list for B2 - cat row



I setup an if statement to check to see what A1 was listed as in the C column for each main type. This was put into Cell C2 for the cat row


repeated this step for each animal row but changed "Cat" to the other animals dog and lizard.

  • as a side note I had to place 1 in the C1 column so that the drop down checker in A1 always appears later on.



once this is set only the drop down for the animal selected in Cell A1.

I hid column C so that it wasn't annoying me and the filter still worked.

( Like I mentioned at the beginning of the response it may be better to add a single cell table above or to the left of the table so that the its more user friendly. )



Thanks again for everyones help I hope that dynamic drop downs will eventually be implemented to make this much easier. or if a function would output a drop down so that you could import different data to the drop down from the steps above would be nice as well.


Y'all are awesome!









Reply
User profile for user: Yellowbox
Yellowbox
User level: Level 7
21,939 points

Aug 17, 2021 8:15 AM in response to Mr-Styx

Hi Mr-Styx,


My first thought was that you wanted "cascading" Pop-Up Menus, whereby the first Pop-Up has choices of animals and if you choose Dog, then you will then see only breeds of dog in the next Pop-Up. That is possible in Numbers.

Then I re-read your question: how do I get cell B1 to show only "tabby" if cat is in cell A1 or only "Shepard" if dog is selected in cell A1.


"To show only" was answered by Barry as a Lookup task.

For "cascading" Pop-Ups, SG echoed my first thought.


Please tell us your overall aim so that we can move forward to a solution.


Regards,

Ian.

Reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Aug 16, 2021 11:46 PM in response to Mr-Styx

What you describe seems a Lookup task.


In cell A1, place your pop-up menu with the three choices you listed.


On a second table, list those choices in column A, and the matching list of results in column B:


The image below has four copies of the 'choice table, all with the same formula, shown in the image, as it appears in the formula editor in cell B2. Each of the choice tales has a different animal selected.


The Data table has no formulas. All data on this table has been entered directly.


For convenience, here is a text version of the formula used.


IF(A2="","",INDEX(Data::B,MATCH(A2,Data::A,0)))


Rename the second table "Data" or change that name to match the name of your Data table before pasting the formula into the Choice table. The choice name is not used in the formula, so a different name will not require a change to the formula.


The two tables can be on the same sheet, or you can place them on separate sheets, with no change to the formula, provided there is only one table named "Data" in the document.


Regards,

Barry


PS: If the comma ( , ) is used as the decimal separator in your region, replace the commas in the formula with semi colons ( ; ).

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Numbers - Text dropdown conditional selection.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up