Apple Intelligence is now available on iPhone, iPad, and Mac!

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

Help with numbers pop up menu

Good morning!


I am trying to do the following:


I have created a pop up menu in cell C1 with 3 options.


Is there a way to set some values in cells E1, E2 and E3 that will change aaccording to the selection in C1?


For example, if C1 is set to " New products ", I would like E1,E2 and E3 to show certain values while when choosing "Used products", the E1,E2 and E3 values will change accordingly.


Thank you!

Posted on Mar 31, 2020 4:26 PM

Reply
Question marked as Top-ranking reply

Posted on Mar 31, 2020 5:29 PM

What is the third option in the pop-up menu?


This is most easily handled with a lookup table (here named Products), and a MATCH-INDEX formula in each cell of column E of the main table. One major advantage of using such a table is that when your products change you can edit the lookup table to replace the old products with new ones.


The example shows two copies of the same pair of tables, with only the Pop-up menu choice changes in C1 (and rthe resutinf changes in contents of the other cells.


Cell C1 of Main contains the three item pop-up menu. Cells E1, E2, E3 and E4 contain the formula shown below the table.

See note below for adjustment of the formula to fit your description (with no header row on Main).


For a Main table like the one below, use the revised formula shown below the tables

Note that the lookup table (Products) does not change.


Regards,

Barry

4 replies
Question marked as Top-ranking reply

Mar 31, 2020 5:29 PM in response to George_Carlin

What is the third option in the pop-up menu?


This is most easily handled with a lookup table (here named Products), and a MATCH-INDEX formula in each cell of column E of the main table. One major advantage of using such a table is that when your products change you can edit the lookup table to replace the old products with new ones.


The example shows two copies of the same pair of tables, with only the Pop-up menu choice changes in C1 (and rthe resutinf changes in contents of the other cells.


Cell C1 of Main contains the three item pop-up menu. Cells E1, E2, E3 and E4 contain the formula shown below the table.

See note below for adjustment of the formula to fit your description (with no header row on Main).


For a Main table like the one below, use the revised formula shown below the tables

Note that the lookup table (Products) does not change.


Regards,

Barry

Apr 1, 2020 11:13 AM in response to George_Carlin

No. The products lists could be placed on the same table. Whether that would be more useful would depend on the purpose of popup list and of the generated list in E1, E2 and E3,


In the single table example below, I've inserted an empty column (F) between the 'view list' part of the table and the 'products lists' part. The formula below the table has beed edited to fit the new arrangement.

From it's location in E2, it can be filled down to the rows below. Placing a copy in E1, a header row cell, requires copying it from one of the non-header cells, then pasting into E1.


Regards,

Barry

Help with numbers pop up menu

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