how to specify a pop up based on previous pop up selection

Question

Level 1

4 points

how to specify a pop up based on previous pop up selection

I want to create a spreadsheet where in cell A1, there’s a pop-up of car dealerships already in alphabetical order. Now, in cell B1 I want to have a pop-up of car makes in which I’m interested and are available at the selected dealership from A1. In C1, I want to have the different models available for the corresponding make selected in B1. In cell D1 I want to have a pop-up of the particular color names for model selected in C1. Finally in cell E1 price ranges can be presented in another pop-up list.

This model is similar to what you’ll encounter at a dealership’s website but I’m looking to have this as a tool for various applications in a spreadsheet format.

I have all data points already housed in individual cells in another sheet ready to plug in—I want to use this as a comparison overview as I conduct my research.

I’m aware that I could use the autofill function that engages as you type but want to see if I can have a more streamlined UX.

I’ve looked around and haven’t seen anything like this yet.

Thanks in advance to anyone who responds.

iPhone 14 Pro

Posted on May 15, 2025 5:26 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

May 16, 2025 12:18 AM in response to Aprendiz_Mac

Numbers does not have native capability to do "cascading menus" as you describe.

It cannot dynamically populate a Pop-Up Menu from a range of cells (the way you can dynamically update a drop-down in Excel from a range of cells.)

However, with the new dynamic array "spill" functions FILTER, SORT, and UNIQUE, you can get much closer than was possible before. You manually choose and enter a value from a dynamic list of filtered values. The lists change automatically depending on the choices you make.

Here's an example:

You need to set up a complete data table from which to make the choices.

In the second table you enter your choice in each cell of row 2 from the values that automatically appear in the cells below in that column.

In B3:

=SORT(UNIQUE(Data::A))

In C3, filled right to F3:

=SORT(UNIQUE(FILTER(Data::B, Data::A=B$2, "No Results")))

The cells in the bottom row check that you have made a valid choice on row 2 of that column, and alert you if you have entered an invalid value.

In B12, filled right:

=IF(COUNTIF(B,B$2)=0,"invalid choice","✅")

SG

Reply

how to specify a pop up based on previous pop up selection

Similar questions