pmc44
Level 1 (8 points)
iWork

Q: Numbers: Drop-down menu choice

For numbers.

I'm creating a database and in one of the column there's going to be a dropdown menu made with a few choices to pick from.  When picking one of the choices, I'd like for that line to be copied and added to another tab (page 2). 

 

Is this possible to do?

MacBook Air, iOS 9.3.4

Posted on Sep 11, 2016 11:10 AM

by t quinn,Solvedanswer
t quinn t quinn
Level 5 (5,012 points)
Mac OS X
A:

Hi pmc,

 

Here is an approach that uses an index column in the original table. The index can be hidden.

Screen Shot 2016-09-13 at 7.23.58 PM.png

D2= C2&COUNTIF(C$2:C2,C2)

 

Then we use that index to bring the info over according to the row #.

Screen Shot 2016-09-13 at 7.18.33 PM.png

A2= INDEX(Table 1::A,MATCH($C$1&ROW(cell)−1,Table 1::$D,0))

This looks for a match of C1 and the row minus 1 in the index column and returns the value found in column A. When you fill it down and across it will bring those values also. I could have filled in column C but it seemed redundant.

This will throw an error when there are more rows than types. I would use a filter to hide those.

 

quinn

Posted on Sep 15, 2016 8:03 AM

Close
pmc44

Q: Numbers: Drop-down menu choice

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Sep 11, 2016 5:44 PM in response to pmc44
    Level 5 (5,012 points)
    Mac OS X
    Sep 11, 2016 5:44 PM in response to pmc44

    Hi pmc44,

     

    If you are wanting to link to a dropdown menu in a cell on one table and have it show up on the table you linked from, the answer is yes. I suspect that what you are wanting to do is a little different. Can you post screenshots of the two tables?

     

    quinn

  • by pmc44,

    pmc44 pmc44 Sep 13, 2016 11:29 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 13, 2016 11:29 AM in response to t quinn

    Thanks for responding!  Here is the screen shot.  As you can see, drop down menu allows for different categories to pick from.  When picking Core, I want that line item to be replicated in Sheet 2.

     

    I did a cut and paste for show... however I'd like to automate this as I have about 5000 line items.  Easies this way + I dont want to sort everytime...

     

    Hopefully this clarifies my intentions.

     

    Screen Shot 2016-09-13 at 2.27.13 PM.png

  • by t quinn,Solvedanswer

    t quinn t quinn Sep 15, 2016 8:03 AM in response to pmc44
    Level 5 (5,012 points)
    Mac OS X
    Sep 15, 2016 8:03 AM in response to pmc44

    Hi pmc,

     

    Here is an approach that uses an index column in the original table. The index can be hidden.

    Screen Shot 2016-09-13 at 7.23.58 PM.png

    D2= C2&COUNTIF(C$2:C2,C2)

     

    Then we use that index to bring the info over according to the row #.

    Screen Shot 2016-09-13 at 7.18.33 PM.png

    A2= INDEX(Table 1::A,MATCH($C$1&ROW(cell)−1,Table 1::$D,0))

    This looks for a match of C1 and the row minus 1 in the index column and returns the value found in column A. When you fill it down and across it will bring those values also. I could have filled in column C but it seemed redundant.

    This will throw an error when there are more rows than types. I would use a filter to hide those.

     

    quinn

  • by pmc44,

    pmc44 pmc44 Sep 15, 2016 8:03 AM in response to t quinn
    Level 1 (8 points)
    iWork
    Sep 15, 2016 8:03 AM in response to t quinn

    Thank you so much for doing this.  It works!

     

    Thanks T Quinn!!!  Good Karma on you!

  • by t quinn,

    t quinn t quinn Sep 15, 2016 4:41 PM in response to pmc44
    Level 5 (5,012 points)
    Mac OS X
    Sep 15, 2016 4:41 PM in response to pmc44

    Hi pmc,

     

    Glad to do it. I love indexes. Thanks for the check!

     

    quinn