Dynamic Pop-up Menus

Hi GW,

Creating a Pop-Up Menu in Numbers 3 is very easy.

In a spare table or column, enter the values that you want to populate the Pop-up.

Select those cells.

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

Done. They are now Pop-Up menus

Click on the upside-down triangle to show the menu items

I would like to have a pop-up menu (on a different sheet) read:

Banana

Orange

Apple

You can now copy and paste any one of those cells to another table (on the same or a different sheet). The menu choices will be carried over.

is it also possible to have the pop-up menu update itself automatically? Let's say I add A4 = Pineapple to my table. Could I get my pop-up menu to automatically have "Pineapple" as an option or must I manually type it in as a selection?

No. Not automatically. Numbers does not allow dynamic update from a table to a Pop-Up Menu.

(Apologies to quinn. I misread the original question).

To add an item to a Pop-Up, use the add button:

Or start from scratch with a new list

Regards,

Ian.

quinn,

Not exactly what you were asking for (yet), but maybe you and Ian could have a look at this proof of concept.

1. Set up "submenu" tables as shown, and format them as Pop-Up Menu.
2. Add a two-column data-entry table.
3. Add values in column A of the data entry table that match the names of the "submenu" tables.
4. Copy script into AppleScript Editor and run.

The result should be Pop-up Menu cells in column B whose items depend on what was entered in column A.

If it works on your end, then can refine to make it easier to apply to "real-world" situations.

(The Fruits table isn't used in this example and there are no "submenus" tables for Apple and Pineapple).

SG

property dataTable : "Table 1"
property subMenuCol : 2
tell application "Numbers"
  set s to front document's active sheet
  tell s to set t to table dataTable
  tell t
  repeat with i from 2 to count rows
  try
  tell s's table (row i's first cell's value)
  set selection range to last cell
  activate application "Numbers"
  tell application "System Events" to keystroke "c" using command down
  end tell
  set selection range to column subMenuCol's cell i
  tell application "System Events" to keystroke "v" using command down
  end try
  end repeat
  end tell
end tell

Hi Ian,

Thanks for the boost. I was thinking that it wasn't going to address GW's issue.

GW- the link is here:

Re: changing Pop-up-Menu

Let me know if it works for you.

quinn

Hi SG,

If I am reading that right it will take A2:A5 and make them into popups.

What if I want to take those popup values and paste them into a table, say this table:

the blank cells are the popups that need updating. can that be done with a script? Still read as a blank cell but with the new popup?

quinn

Hi Applescript Guru SG,

I tried your script with this table in Numbers 3.2 (and Numbers '09 was not open)

A2 to A5 selected. In AppleScript Editor I pasted the script and clicked on the hammer to compile.

Syntax Error. Expected variable name, class name or property but found unknown token.

Same message if I click on Run.

Same message if I click somewhere else in the table (A2:A5 are not selected).

Thanks for your help.

Regards,

Ian.

Hi Ian,

That sounds like a copy-pasting problem. (I find the new forum software particularly unfriendly to AppleScript). I suggest trying the copy-paste again. After hitting the hammer, the script should look like this:

You don't have to select A2:A5 manually. Just click anywhere in the table and run.

SG

Hi SG,

You wrote: I find the new forum software particularly unfriendly to AppleScript

Maybe not the new forum software. I copied your script to Pages 4 and saved. Then copied from Pages 4 to the AppleScript Editor. No go.

But copying your script from your first reply above, then pasting into AppleScript Editor did exactly as you said in your second reply.

A2:A5 are now Pop-Up Menus.

Thanks!

Regarding the new forum software,

Site map of Communities and Categories

Regards,

Ian.

SG,

This is working well for me. I changed "Table 1" to "Checklist" here

property dataTable : "Checklist"

I don't quite understand how this script works but here is my understanding of what it does:

Looks for value in dataTable Column A that matches a table name on the same sheet

Copies the value of the last entry in named table into column B where the match is found.

Since the script only takes the last entry in the table I tried using "Microphone" as a single cell table. While I don't see it as best practices, I would be tempted to just edit the popup there. And it propagates to wherever Microphone is in column A. As a note, the mixer table's last cell is a popup with the blank chosen.

If the "2" in propertysubMenuCol : 2 is changed to "1", then "Microphone" would be overwritten with the popup from the Microphone table. This is great. I can call the popup to any column A cell if I enter the table name of the popup.

How would I adjust this script if I wanted it to look in column C for the popup table name?

This seems to be getting really close in answering GW's (and so many others') question about dynamic popup menus. It does, in fact, do what he has asked except the table needs to be on the same sheet and once you have made a selection your popup won't update.

quinn

Hi SG,

This is very neat stuff. I am starting to understand how you are putting these things together.

I am experiencing a glitch in that I have to run the script multiple times for it to completely do its job. Here is a screenshot after the first run of the script:

If I run it 4 times I will get all the popups in column C to propagate. Running Activity Monitor doesn't show anything getting overwhelmed. I am at a loss as to why this is (or isn't) happening. Any Ideas?

quinn

Hi SG,

I am not so sure there is an error. I removed the try/end try and script ran fine- except for the missed pastes. Here is the Event from one of those missing cells:

I am seeing what you mean about this forum software and scripts. This is a screenshot. Seems to be showing it is doing what it is supposed to.

On anoather note, The script works substantially better if I convert my control column to text first.

quinn

Hi SG,

Easier to just show you. Here is my solution for a 3 deep conditional popup.

Toni popup.numbers

The one I have been playing with is only 2 deep. I have broken its functionality at the moment or I woud load it up.

What I would like is to be able to overwrite the B column with refreashed popups. this may not be trustworthy on my machine. Here are the popup tables from the doc I have been playing with. You can see sheet 1 here: in response to t quinn.

Where would I use the delay statement in this script of yours? It might be all it takes for this case?

quinn

Where would I use the delay statement in this script of yours? It might be all it takes for this case?

Try it here:

Increase the value if it still skips some rows. Decrease the value if you can get away with it on your machine. The script is a bit pokey but it does get the job done here:

SG

Hi quinn,

As you know (we have corresponded by email) I have been working on your idea for two-level "nested" (or "dynamic" or "cascading") Pop-Up Menus using Numbers (not AppleScript).

Thank you for your suggestion (by email) that an Order Form should allow a customer to order more than one item. I played with that idea and decided that it is outside the simple scope of one nested Pop-Up Table. We need a new table for each product, or a much more complex document.

I am now thinking of several tables in one document, each table for one order. Include a summary table (Invoice).

Regards,

Ian.

Hi SG,

Yes, Those are blank cells.

This is an expanded version of a solution I offered here:

Problem with Formula - multiple outcome IF then statement all the popups are in one column, with a show column revealing the next choice.

I have been holding out hope that I could have your script overwrite the lone popup column.

quinn

Hi Kilgor,

There are ways to flag duplicate values but the popup aspect to your question is a nonstarter. As stated we do not have dynamic popups in Numbers.

Here is a suggestion that may help with your issue.

Find duplicates

Combined with conditional highlighting you can get pretty close to what you are asking for.

Conditional Formatting

You would substitute

=IF(COUNTIF($A,A2)>1,A2"") f

or SG's formula in Find Duplicates and then follow my suggestions in the conditional formatting thread.

quinn