User profile for user: dnnssgl
dnnssgl Author
User level: Level 1
13 points

Create a drop down based on a list in Numbers

Hi,


I was wondering whether it is possible to create a drop down that is populated with entries from another table. For instance, there is a list "product" with "weight" and "price" columns. I want to pick a "product" from this very table in a drop down and in the next step get the weight and price information.


I figured out that it is not possible to realize with the basic drop down since one has to create every entry manually. I was hoping though that there is some function workaround.


Best,

Dennis

Posted on Dec 9, 2017 4:19 AM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Posted on Dec 30, 2017 6:27 AM

Hi Dennis,


So essentially you want to Select a row name from one pop-up menu, Select an amount (mass) from a second pop-up menu, and get the value at the intersection of the named row and the named column in a lookup table.

User uploaded file

Articles:


This is a Lookup table, containing no formulas.*

Labels in column A must and in row 1 must exactly match the labels in the two pop-up menu sets in Orders.


Orders:


  • Column A: entered data. Not affected by or used by formulas.
  • Column B: Each body cell is a popup menu containing a 'blank' and four menu items matching the four labels in column A of the lookup table, Articles.
  • Column C: Each body cell is a popup menu containing a 'blank' and three menu items matching the three labels in row 1 of the lookup table, Articles.
  • Column D: Each body cell contains the formula shown below the tables, entered in D2, then filled down to D10.


Formula:


D2: IF(OR(B2="",C2=""),"",INDEX(Articles::A:D,MATCH(B2,Articles::$A,0),MATCH(C2,Articles::$1:$1,0)))


The core formula, shown in bold, uses MATCH to generate a row index number and a column index number.

The two index numbers are returned to INDEX, which retrieves the value from the cell at the intersection of the indexed row and the indexed column.


Example: In Orders::D2:

  • The first MATCH gets "Article 1" from cell B2, searches for that value in Column A of Articles, finds it at the second position in the list in that column, and returns 2 to INDEX.
  • The second MATCH gets "50g" from cell C2, searches for that value in Row 1 of Articles, finds it at the second position in the list in that row, and returns 2 to INDEX.
  • In each MATCH, the final argument, 0, tells MATCH to accept only an exact match. If the search value is not found, MATCH will return an error.
  • INDEX uses the two values (2 and 2) returned by MATCH, and retrieves the value ( 50 )at the second row and the second column of Articles.


The outer part of the formula (shown above in normal type) is a switch, used to prevent calculation by the core formula until there is an entry selected in both of cells B2 and C2.


Regards,

Barry

View in context
5 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Dec 30, 2017 6:27 AM in response to dnnssgl

Hi Dennis,


So essentially you want to Select a row name from one pop-up menu, Select an amount (mass) from a second pop-up menu, and get the value at the intersection of the named row and the named column in a lookup table.

User uploaded file

Articles:


This is a Lookup table, containing no formulas.*

Labels in column A must and in row 1 must exactly match the labels in the two pop-up menu sets in Orders.


Orders:


  • Column A: entered data. Not affected by or used by formulas.
  • Column B: Each body cell is a popup menu containing a 'blank' and four menu items matching the four labels in column A of the lookup table, Articles.
  • Column C: Each body cell is a popup menu containing a 'blank' and three menu items matching the three labels in row 1 of the lookup table, Articles.
  • Column D: Each body cell contains the formula shown below the tables, entered in D2, then filled down to D10.


Formula:


D2: IF(OR(B2="",C2=""),"",INDEX(Articles::A:D,MATCH(B2,Articles::$A,0),MATCH(C2,Articles::$1:$1,0)))


The core formula, shown in bold, uses MATCH to generate a row index number and a column index number.

The two index numbers are returned to INDEX, which retrieves the value from the cell at the intersection of the indexed row and the indexed column.


Example: In Orders::D2:

  • The first MATCH gets "Article 1" from cell B2, searches for that value in Column A of Articles, finds it at the second position in the list in that column, and returns 2 to INDEX.
  • The second MATCH gets "50g" from cell C2, searches for that value in Row 1 of Articles, finds it at the second position in the list in that row, and returns 2 to INDEX.
  • In each MATCH, the final argument, 0, tells MATCH to accept only an exact match. If the search value is not found, MATCH will return an error.
  • INDEX uses the two values (2 and 2) returned by MATCH, and retrieves the value ( 50 )at the second row and the second column of Articles.


The outer part of the formula (shown above in normal type) is a switch, used to prevent calculation by the core formula until there is an entry selected in both of cells B2 and C2.


Regards,

Barry

Reply
User profile for user: dnnssgl
dnnssgl Author
User level: Level 1
13 points

Dec 9, 2017 6:12 AM in response to Yellowbox

Hi Ian,


thanks for your reply! The tutorial helped me in so far that I learned how to populate a pop up menu with existing data. I don't need the list to be updated - would be nice though.

User uploaded file

Scenario: If I select Article 1, let's say I need 100g, cell D2 should output the correct price, like in this example:

User uploaded file

How would I define the function? D2 is based on what I selected in B2 and C2

Reply
User profile for user: Barry
Barry
User level: Level 9
60,023 points

Dec 9, 2017 6:22 PM in response to dnnssgl

Hi Dennis,


How is the price in D2 dependent on the entries in B2 and C2?


Given the numbers in the table, how do you determine a price of 1 for 100g of Article 1?

What calculation is needed to derive this price from this quantity of this item?


For the same item (Article 1), what would be the prices of these amounts:


10g

25g

50g

75g

100g

150g

500g

1kg

2kg

2500g


Regards,

Barry

Reply
User profile for user: dnnssgl
dnnssgl Author
User level: Level 1
13 points

Dec 11, 2017 6:29 AM in response to Barry

Hi Barry,


I used some random numbers but it really doesn't matter how these numbers are determined. Otherwise I would use some math operation to solve this problem.


My new approach is to somehow use if / else conditions and references to get my desired value. All I need to know is how to use INDEX(), CELL(), ROW() properly.


Example: if Table2:B2 equals Table1:A2 look if Table2:C2 equals Table1:C1 then take Table2:C2 or if Table2:C2 equals Table1:D1 then take Table2:D2. Else if Table2:B2 equals Table1:A3 .. you get the idea.


Best,

Dennis

Reply

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Create a drop down based on a list in Numbers

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