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.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
Is there a way to give options in a Pop-up menu a numerical value so that once selected, the corresponding value appears in an adjacent cell?
For example:
Pop-up menu options in Cell A1:
(1) Fruit
(2) Vegtable
If I selected “(1) Fruit” in Cell A1 a “1” would appear in Cell A2.
Any help would be greatly appreciated,
Stu
A simple way that generally does not require cluttering up your document with extra tables is to use the SWITCH function:
=SWITCH(A2,"fruit",1,"vegetable",2)
This is easy to edit. Let's say you decide to add "meat" and assign it a 3. Simply double click the cell to call up the formula editor and change the formula to:
SWITCH(A2,"fruit",1,"vegetable",2,"meat",3)
As you can see, you simply have a reference to the cell with the Pop-Up Menu, in this simple example A2, and follow that by switch-value, if-match pairs. You can have many pairs.
More on SWITCH here (also accessible via Help > Formulas and Function Help in your menu):
You can get fancier and add a default value for when there are no matches, etc., but generally that is not needed because Numbers will warn you automatically if there are no matches.
Substitute ; for , in the above formulas if your region uses , as a decimal separator, e.g.:
=SWITCH(A2;"fruit";1;"vegetable";2)
SG
A simple way that generally does not require cluttering up your document with extra tables is to use the SWITCH function:
=SWITCH(A2,"fruit",1,"vegetable",2)
This is easy to edit. Let's say you decide to add "meat" and assign it a 3. Simply double click the cell to call up the formula editor and change the formula to:
SWITCH(A2,"fruit",1,"vegetable",2,"meat",3)
As you can see, you simply have a reference to the cell with the Pop-Up Menu, in this simple example A2, and follow that by switch-value, if-match pairs. You can have many pairs.
More on SWITCH here (also accessible via Help > Formulas and Function Help in your menu):
You can get fancier and add a default value for when there are no matches, etc., but generally that is not needed because Numbers will warn you automatically if there are no matches.
Substitute ; for , in the above formulas if your region uses , as a decimal separator, e.g.:
=SWITCH(A2;"fruit";1;"vegetable";2)
SG
If it is a very small list you can do all in the one cell. If it is a larger list or one you will be adding to or modifying, you would use a lookup table. The screenshot has both methods.
Row 2 is the method that does it all in one cell (it does not use Table 2).
Table 1::A2 =XLOOKUP(A1,{"fruit","vegetable"},{1,2},"not found",0)
drag-fill the formula to B2
Row 3 uses the lookup table (Table 2)
Table 1::A3 =XLOOKUP(A1,Table 2::$A,Table 2::$B,"not found",0)
drag-fill the formula to B3
With the lookup table you can add new items to the lookup table, change the number associated with an item, or make other changes without having to modify any formulas.
Numbers Noob> Can I give Pop-up menu options a numerical value?
