You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Variable Pop-Up Menu cell

Hi,


I have a spreadsheet with all my customers' data (name, TIN, address...), and another with my sales. I want to assign each sale to one of my customers using a Pop-Up menu cell. The thing is that I always keep adding new customers to that list, so ideally the Pop-Up options should change.


I guess that's not possible... but any idea?


Thanks, guys!!

Dani

MacBook Pro 15″, macOS 13.3

Posted on Mar 27, 2023 3:13 AM

Reply
Question marked as Top-ranking reply

Posted on Mar 27, 2023 7:49 AM

You can do that pretty easily in Excel. It does "pop up" menus differently. The "items" can be listed in a table and, when you add an item to the list, all the "pop up" menus based on that list will automatically include the new item. All you need is a table of the items you want in the "pop up" and to set up Excel's data validation wherever you want the "pop up".


You can do something similar, but not quite, in Numbers but it is a few steps. In Numbers, you can create a pop up menu from a list in a table (which is the easiest way to do it anyway). Unfortunately, though, new items added to the list will not be included in existing pop up menus. Also, you cannot paste it over pop ups you are already using or it will change the selected item. You can only copy/paste it to cells with unused pop ups. So you will end up with different cells having different pop ups. I think with AppleScript it may be possible to overcome this limitation.


Create the initial pop up menu

  1. List the items in a column of a table
  2. Select them all
  3. Format as pop up menu. All will become a pop up menu of all the items.
  4. Copy/paste the pop up to cells that need it


Add new items to the pop up menu (similar procedure can be used to delete or rename items)

  1. Add new items to the column
  2. Sort the column (if needed/desired)
  3. Select all the items
  4. Format as text, number, date or whatever, depending on the type of data in the list
  5. Format as pop up menu. The new pop up menu should be in sort order.
  6. Copy/paste the new pop up to the cells that need it, overwriting not-yet-used old pop ups. Do not do this to the ones already being used or their selected item will get changed.


6 replies
Question marked as Top-ranking reply

Mar 27, 2023 7:49 AM in response to Danval31

You can do that pretty easily in Excel. It does "pop up" menus differently. The "items" can be listed in a table and, when you add an item to the list, all the "pop up" menus based on that list will automatically include the new item. All you need is a table of the items you want in the "pop up" and to set up Excel's data validation wherever you want the "pop up".


You can do something similar, but not quite, in Numbers but it is a few steps. In Numbers, you can create a pop up menu from a list in a table (which is the easiest way to do it anyway). Unfortunately, though, new items added to the list will not be included in existing pop up menus. Also, you cannot paste it over pop ups you are already using or it will change the selected item. You can only copy/paste it to cells with unused pop ups. So you will end up with different cells having different pop ups. I think with AppleScript it may be possible to overcome this limitation.


Create the initial pop up menu

  1. List the items in a column of a table
  2. Select them all
  3. Format as pop up menu. All will become a pop up menu of all the items.
  4. Copy/paste the pop up to cells that need it


Add new items to the pop up menu (similar procedure can be used to delete or rename items)

  1. Add new items to the column
  2. Sort the column (if needed/desired)
  3. Select all the items
  4. Format as text, number, date or whatever, depending on the type of data in the list
  5. Format as pop up menu. The new pop up menu should be in sort order.
  6. Copy/paste the new pop up to the cells that need it, overwriting not-yet-used old pop ups. Do not do this to the ones already being used or their selected item will get changed.


Mar 28, 2023 3:13 AM in response to Danval31

Hi Dani,


Danval31 wrote:

The thing is that I always keep adding new customers to that list, so ideally the Pop-Up options should change.

Numbers is not so different from Excel! The list is available under Format > Cell > Data Format. That is where you add new menu items.


I have selected all Pop-Ups in Column B to show that they are all the same, including the unused cells in B6 and below. That is the reason for Start with Blank (which appears as the menu item "None" in all of those Pop-Ups).


Click on the plus sign in the format panel to add a new customer to the list:


and type the new customer name (I typed Customer 4). That now appears in all the selected Pop-Ups without changing their settings:



Regards,

Ian.

Mar 28, 2023 3:42 AM in response to Yellowbox

Thanks Ian, but I don't understand your point at all.


Let's say I have this Customers table where I manually add all my customers' data (A and B columns are filled manually):


I'd like to have this Sales table referring to that Customers::B column, so it'd show something like this:

Ideally, that Sales::C Pop-Up Menu should update automatically each time I add new data in Customers table.


Thanks for your help.

Dani

Mar 28, 2023 12:49 AM in response to Danval31

Danval writes: "A pity that we can't include new items automatically..."


Not yet, anyway. But you can use the Provide Numbers feedback menu item in the Numbers menu to send a New Feature or Feature enhancement request to Apple. A brief explanation of where the new feature would be useful and how it would improve your workflow could improve the likelihood of your suggestion being acted on.


Regards,

Barry

Mar 28, 2023 5:39 AM in response to Danval31

That is how it would work in Excel.


In Numbers, the items in the pop up menu are "hard coded" into the cell format. It would be pretty nice if, instead of a hard-coded list of items, you could specify a range of cells. Then it would do what you want. As it is, you have to create a new pop up and copy/paste it to the cells manually.


Numbers->Provide Numbers feedback

Variable Pop-Up Menu cell

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