Numbers iPad App: Linking (2) PopUp Menu Cells using "IF" or Other Formula

Question

Level 1

18 points

Numbers iPad App: Linking (2) PopUp Menu Cells using "IF" or Other Formula

Hi all, this might not be possible. But I keep my fingers crossed. Thank you for your patience with me as a basic Numbers user.

Pls note: I am not on a Mac. I am using the Numbers app on my iPad and or iPhone. (That can change the answer I know)

MY GOAL:

I want to be able to choose from a drop-down list a.k.a. pop-up menu of "Categories" in the 1st Cell....& ••instead of•• •manually• choosing in the 2nd Cell from the list of colors for my inventory color coding system.... I want numbers to associate the correct colors with each category so that IE: if the user chooses "Shoes" @Cell A ~ then numbers automatically fills in "Light Blue" @Cell B as the color (or) IE: if the user chooses "Clothes" ~ the numbers will automatically fill in "Purple" as the color.

(this the user does not need to memorize the color coding system used for the inventory.... numbers will have the system already built in from some reference drop-down pop-up list of the 12 color options that match up to the 12 category options)

This might be not possible

I hope this makes sense

SO FAR (...no success yet)

I created (2) pop up menus (drop down lists) in (2) adjacent cells. Each cell has the same quantity of options. Cell A = List of Categories (w/varied text titles: similar but not duplicate) Cell B = List of Colors (again, some similar text such as "light pink" or "dark pink", but nothing duplicated)
My hope is to create a formula that will automatically fill in Cell B ~ (choosing from the color values available from the list created in the Cell B pop-up) = Based on which Pop-Up Value is selected in Cell A.
My gut feeling says I might need to have an extra empty Cell that contains the actual formula to be filled in as a result ~ to connect (link?) Cell A & Cell B = perhaps it will require Cell C to be the value resulting from what is chosen in Cell A ~ linked to/pulled from Cell B (as a reference point only?) = to then Auto-Fill the correct (Color) Value in Cell C...? because I am guessing I can't have a pop-up menu existing in Cell B, and ALSO have a formula written for Cell B's value, allowed to pull from the drop-down I created in Cell B (because it will reference itself)... that's my guess... perhaps Cell B is a hidden cell used only as a reference of values to pull from to automatically determine the correct color and fill it into the visible Cell C ~ (yes?)
And most importantly… Is this even an "IF" type formula? and if so, how do you create or write that formula correctly without a syntax error? (you could imagine that's what I keep getting every way I try!)

TYVM!!! :-)

iPad Pro, iPadOS 18

Posted on Feb 4, 2025 8:17 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Feb 6, 2025 12:49 PM in response to JaniceDDodge

> YES, there is just ONE same static option matched up between the 2 drop downs.

Then it's easy.

There are actually multiple ways of doing that. Which one to choose depends a lot on the number of items you have to deal with, and how often they change.

If it's more than just a coupe of options, my recommendation would be to create a separate table of the options:

This can be as long as you like, and the values in column A should match the items in your popup menu.

Now, back in your main table, the Category cell continues to be a pop-up menu, but the Color cell now uses XLOOKUP() to find a matching entry from the category table:

in this case, cell B2 contains the formula:

=XLOOKUP($A2,Categories::A,Categories::Color," ",-1,-1)

This tells Numbers to take the value in cell A2 and find it in Column A from the Categories table.

For any match, it returns the corresponding item from column B in the Categories table - namely, the color choice.

The rest of the function tells XLOOKUP what to do if there's no match found (return a blank string),and how to perform the search (exact match, top-to-bottom).

Just fill that formula down the column and it will pick up the available color choices based on Column A.

Reply

Answer 2

Camelot

Level 9

58,299 points

Feb 4, 2025 10:58 AM in response to JaniceDDodge

If there are multiple options in B for each value of A, there's no practical way of doing this with pop-ups.

In Numbers, Pop-up menus can only be based on a fixed list. That means that the pop-up in column B will always have the same options, and there's no way to change/limit the options based on the value of column A. Therefore the popup in B would have all the options, not just the options that are relevant for the current value in A.

Now, if there's only one color/B option per category/A value, then you could construct a formula that pulls the right option (e.g. if category/A = 'Ball' then color/B = "Blue"), but you can't make the popup in B offer "Blue" and "Green" for Ball, and "Red" and "White" for hats.

Reply

Answer 3

Yellowbox

Level 7

21,939 points

Feb 4, 2025 12:31 PM in response to JaniceDDodge

Hi Janice,

See if this does what you want:

"Nested" or "Cascading" Pop-Up Menus in N… - Apple Community

That was written for Macs. Sorry, I don't have an iPad to test.

Regards,

Ian.

Reply

Answer 4

JaniceDDodge Author

Level 1

18 points

Feb 4, 2025 8:19 AM in response to JaniceDDodge

oh, PS: in case this matters… Usually does… I am on iPad Pro 12.9 sixth generation running iOS 18.2.1 thank you so much for helping a girl out trying to make it easier for everyone and less to memorize!

Reply

Answer 5

Camelot

Level 9

58,299 points

Feb 4, 2025 1:22 PM in response to Yellowbox

Hmm.. an interesting technique... basically building an array of all the possible permutations in a series of rows, then hiding the pop ups that don't match the first.

Not a bad approach, as long as the hidden rows don't get in the way of the rest of the sheet. Best setup as a table on its own.

Still would be nice if Apple let us use dynamic popup menus rather than static lists.

Reply

Answer 6

JaniceDDodge Author

Level 1

18 points

Feb 4, 2025 6:57 PM in response to Camelot

good clarity ~ Thank you for your help & reply. YES, there is just ONE same static option matched up between the 2 drop downs. "Shoes" would always be "Light Blue" or "Clothes" would always be "Dark Pink" every time. I have 11 categories & 11 colors, I also listed them in the matching order in the 2 pop up drop downs.... I want to be able to choose from Cell A drop down pop up list = the Category ~ then have Numbers know by formula to auto fill in "Dark Pink" from (a list of colors) every time I choose "Clothes" ~ do not need "Ball" category to sometimes be "Red" & sometimes be "Blue" = it would always need to = "Red" .... this allows the user to choose a category (which is easy to know).. but they wouldn't have to memorize "which color" always matches "each category"

IF THERE IS a formula or method for THAT... then WOW!! (for Ipad app sadly)

Thank you for any help

(very sorry for over explaining!!)

Reply