3

I'm trying to steer clear of Excel, despite my sister's nagging, and I've done pretty well to create a Quote and Invoice workbook in Numbers. The problem I am having is working out how to automatically populate and update a pop-over menu.

In Excel, I can choose to create a list based on a range of cells, which is pretty much exactly the function I require. However, in Numbers I can only seem to create plain text items in a pop-up menu and not reference any cells.

For a little bit more info, I have a separate sheet in my workbook that contains item codes, descriptions, and prices. I can reference them normally in the other sheets. I want to be able to select a cell in one sheet and have a list / pop-up menu come up with all the item codes from the other sheet, and have it automatically update if I add a new one.

Is this possible or will I have to switch to that horrid beast Excel?

Improve this question

4 Answers 4

Reset to default
1

I had the same sort of problem and with some help from people within the Apple Support community I came up with this solution. Hopefully it will work for you.

This is my list of players in sheet 1. In your case, the Players name would be the item codes.

Player list

Here is sheet where I want to information copied to. Sheet 2

The P# corresponds to the number next to the Players name in Sheet 1. If I enter a 1 and a 2 the names are filled in to the column to the left of the box. enter image description here enter image description here

This shows the formula required to transfer the players name from sheet 1 to sheet 2. enter image description here

To take it a step further, when I fill in the Players winnings in Sheet 1 it also fills it in all the "Earnings" cells for that player in Sheet 2 using the formula shown below. enter image description here

enter image description here

My list of players goes to 300 but the last 20 or so are empty. That way I can still add more names or in your case, item codes, to it.

This may be more complicated than you require but hopefully it helps you.

Improve this answer
4
  • I presume for this you would have to remember what numbers correspond to which player though, yes?
    – Sera H
    Jan 1, 2015 at 19:22
  • Yes, you would have to refer to Sheet 1 that has the list of names. Here is a link to how it was explained to me in the Apple Support forums. discussions.apple.com/thread/5807330
    – J and S Canada
    Jan 4, 2015 at 3:04
  • Having had a proper look at your solution (I was on my iPhone last time), it doesn't actually suit my needs for this particular example. I have a "vlookup" set to auto-populate other fields in my table based on the data in a particular cell, as how you have it above, but I want a drop down list available for that field so I don't have to switch to the secondary sheet to find the right code. I'll update my post to clarify. But thanks for the answer :)
    – Sera H
    Jan 20, 2015 at 20:01
  • This doesn't actually answer the question at all. The purpose of using the pop up menu is to make sure that only valid data is entered. That is very simple to do in excel - the question is how to do it in numbers.
    – Ron Srebro
    Aug 22, 2015 at 18:13
0

This is possible -- see https://discussions.apple.com/thread/6894760

You create a source column with each of your options in a cell, select them and format as a pop up.

You can then copy and paste a single cell from this region to get a cell which is a pop up containing these values.

If you edit a value in the source column you need to repeat the copy and paste.

Improve this answer
1
  • The problem with this solution is that you loose all the values when pasting again, you better use the solution I posted, and use the "copy style", "paste style" thing.
    – spacebiker
    May 13, 2016 at 22:24
0

Yes you can do it,

Thanks to this post https://discussions.apple.com/thread/6894760 I found SGIII's answer which explains how to solve it:

The first step is to put the "Copy Style" and "Paste Style" buttons in the toolbar (right click over the toolbar and personalize it)

enter image description here

Now you can:

  1. Add the new value(s) to your source column
  2. Select the values in the column and choose Data Format > Pop-Up Menu
  3. Select one of the cells in the source column (which now includes the new values in its Pop-up Menu) and click Copy Style
  4. Select cells in the target column, and click Paste Style. The target column will then include the (expanded) Pop-up Menu preserving the previous values.

Voila!

Improve this answer
2
  • This doesn't seem to work in Numbers 4.x
    – Vlad
    Feb 25, 2018 at 12:01
  • This doesn't work (anymore?)
    – Mosselman
    May 18, 2018 at 9:48
0

While not dynamic, MacMost has a helpful workaround:

  1. Select one of the cells using the current pop-up menu, and add a new item in the "Pop-Up Menu" list in the Data Format section, in the Format sidebar, under the Cell tab.
  2. Select all the cells using the earlier pop-up menu, as well as the cell with the pop-up menu you just edited
  3. The Data Format section will now have a message warning you that "The selected cells contain different pop-up menu items." and a button below to "Merge Menu Items"; click the button.

Now all of the pop-up menu items have the new menu item added, and retain their earlier values.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .