Currently Being Moderated

Nested (or Cascading) Pop-Up Menus in Numbers 3

Acknowledgement to t quinn who had the original idea and described the steps required to create nested Pop-Up Menus.

 

This User Tip applies to Numbers 3.2. If you are using an earlier version of Numbers, please start a new question in Apple Support Communities > Numbers for Mac.

 

A frequently asked question in Numbers is how to create "nested" (or "cascading" or "dynamic") Pop-up Menus, where the menu items in the next Pop-Up Menu depend upon the previous choice.

 

The short answer is that Numbers does not support that feature. Pop-Up Menus can not alter their menu items dynamically.

 

A brilliant idea by t quinn gets around this problem:

 

  1. Use a conditional (IF) formula to insert "show" into those table rows that match a previous Pop-Up Menu choice, and then:

  2. Use the Filter feature to show only those rows of a table that contain the value "show" to reveal the next relevant Pop-Up Menu.

 

Example: a shop sells microphones, mixers and recorders. Each product has several levels of quality (Excellent, Good, Basic). A salesperson can use a table with nested Pop-Up Menus to fill out an order form for a customer.

 

Step 1. Create a "Products" Table.

The  "Products" table is an inventory of stock on offer. It also is the basis for creating an "Order Form" table.

Screen Shot 2014-08-24 at 12.56.25 am.png

Save the Products table to allow for future updates to the shop's products.

 

Step 2. Create an "Order Form" table.

Screen Shot 2014-08-24 at 1.02.09 am.png

 

Step 3. The first level Pop-Up Menu.

  • Select Cells A1 to A4 of the Products table.

  • Format Panel > Cell > Data Format > Pop-Up Menu.

  • Select any one of those Pop-Up Menus and Copy (it will contain all you need for the first-level Pop-Up Menu in the Order Form).

  • In the Order Form, Paste into Cell A1.

 

That Pop-Up Menu is now in Cell A1 of the Order Form:

Screen Shot 2014-08-24 at 1.06.53 am.png

 

Type "show" into Cell B1 to prepare the Order Form for Step 6 (the Table Filter).

 

Step 4. Copy the Products into the Order Form.

  • In the Products Table, Select Cells B1 to B4 (Microphones).
  • Convert them to Pop-Up Menus.
  • Select any one of those Pop-Up Menus and Copy.
  • Paste into Cell A2 of the order Form.
  • Repeat with C1 to C4 (Mixers). Copy one Mixer Pop-Up Menu and Paste into Cell A3 of the Order Form.
  • Repeat with D1 to D4 (Recorders). Copy one Recorder Pop-Up Menu and Paste into Cell A4 of the Order Form.

Screen Shot 2014-08-24 at 1.09.35 am.png


Note: don't select all products at once, or you will create a Pop-Up Menu with all 12 items. That is not what you want for nested Pop-Up Menus.

 

Step 5. Use IF formulas to insert "show" into those rows that have been 'chosen' by the Level 1 Pop-Up Menu.

These IF formulas will insert "show" in the relevant rows, ready for the Table Filter.

Note: don't Preserve Row or this will not work if you want to expand it to an order form for multiple purchases. (See Step 8.)

 

B2=IF(A1="Microphone", "show","")

B3=IF(A1="Mixer", "show","")

B4=IF(A1="Recorder", "show","")

 

For example, if the customer chooses 'Microphone' in Cell A1, Cell B2 will contain "show":

Screen Shot 2014-08-24 at 3.09.01 am.png

(Note: B1 already contains "show" because you typed that.)

Now reset A1 to 'Choose a Product' ready for the next step.

 

Step 6. Apply the filter.

 

Screen Shot 2014-08-24 at 1.21.56 am.png

To see this:

Screen Shot 2014-08-24 at 1.41.57 am.png

Save As Template.


When you open a new document from the template, you can choose a product and then a quality of product:

 

Screen Shot 2014-08-24 at 1.43.58 am.png

 

You could Hide Column B, as that does not need to be visible (and could confuse the customer).

 

Step 7. Reset the "Products" table.

Select all the cells in the "Products" table and set the Data Format to Automatic. That table is now ready for editing as the shop changes the products on offer. You can then use that table to create a new "Order Form" from Step 2.

 

Step 8 (optional). Expand the order form to allow multiple purchases.

  • Remove the filter on the Order Form;
  • Select rows 1 to 4 and Copy;
  • Select Row 5 and Menu > Insert > Copied Rows;
  • Repeat as needed.

 

Screen Shot 2014-08-25 at 6.43.20 pm.png

Reapply the Filter

Screen Shot 2014-08-25 at 6.44.53 pm.png

 

************************

Possible future enhancements to this User Tip (all comments welcome):

  • Several levels of 'nested' Pop-Up Menus;

  • How to edit a Pop-Up Menu;

  • AppleScript can automate some of these processes. For example, see this thread where t quinn and SGIII have contributed: https://discussions.apple.com/message/26473194?ac_cid=tw123456#26473194

Replies

Delete User Tip

Are you sure you want to delete this user tip?