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.

Is it possible to have cells change automatically if another cell's pop-up menu is changed?

I am making a spreadsheet where I will have a number items. I decided to organize some of them via pop-up menus. I am wondering if the following scenario is possible:


Let's say I have a number of different items (Item 1, Item 2, Item 3, etc.). Each of these items has 7 different cells of information tied to them. Right now, I am thinking of turning each of the 8 cells into pop-up menus and then manually changing them all. So, If I want to change from Item 1 to Item 3, I would then go to each of the 7 cells below and change them manually.


I am wondering if it is possible to have the 7 cells below change automatically when I change the main pop-up menu cell. So, if I change Item 1 to Item 3, the 7 cells below would change automatically with it.


Is this doable? Thanks.

Posted on Oct 4, 2020 8:00 PM

Reply
Question marked as Top-ranking reply

Posted on Oct 5, 2020 7:48 AM

You could use the INDIRECT function in the table where you select the header column from the original table using the Pop-Up menu, like this:

First create the Pop-Up menu in the header row of the Origin table.


Next copy/paste the Pop-Up menu cell in the header cells of the table "Column selection by Pop-Up" from column B to e.g. column D in this second table:


The two left header columns from both tables have to be identical: ROW2 ... ROW9.


I put the following formula in the cells B2 to D9 of the second table:

INDIRECT("Origin::"&D$1&" "&$A4;)


You can now select columns in the second table by using the Pop-Up menu in the header row cells of the second table.

Enjoy.

Paul.

Similar questions

3 replies
Question marked as Top-ranking reply

Oct 5, 2020 7:48 AM in response to eh-toque

You could use the INDIRECT function in the table where you select the header column from the original table using the Pop-Up menu, like this:

First create the Pop-Up menu in the header row of the Origin table.


Next copy/paste the Pop-Up menu cell in the header cells of the table "Column selection by Pop-Up" from column B to e.g. column D in this second table:


The two left header columns from both tables have to be identical: ROW2 ... ROW9.


I put the following formula in the cells B2 to D9 of the second table:

INDIRECT("Origin::"&D$1&" "&$A4;)


You can now select columns in the second table by using the Pop-Up menu in the header row cells of the second table.

Enjoy.

Paul.

Oct 5, 2020 5:27 AM in response to eh-toque

If you want the table to "look up" the data for the item based on the item name, that is a very common thing to do in spreadsheets. Below is an example with two cells of information for each item, easily expanded to seven.



The Product Data table is all manually typed in. This is the data for each item.

In Table 1,

Column A is formatted as popup menu with the three item names

Column B formula =IF($A2<>"",XLOOKUP($A2,Product Data::$A,Product Data::B,"Not Found"),"")

Fill that to the right and fill down to complete the rest of he table


One way to create a pop-up of a long list of items:

  1. Put the list of items in a column of a table and sort them into the order you want them to appear in the menu. In the example above, the items are already in column A of in the Product Data table so I would just use that column.
  2. Select all of the cells that you want in the pop-up menu.
  3. Change the format to pop-up menu. This will format all those cells as pop-up menus with all the selected items in the menu. You may not want this column of cells to be pop-ups; you can reverse this later.
  4. Copy one of the cells.
  5. Use Undo (Cmd Z) to change the format of the column back to what it was.
  6. Paste the pop-up to where you need it.

Is it possible to have cells change automatically if another cell's pop-up menu is changed?

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