6 Replies Latest reply: Feb 10, 2013 6:30 AM by Nutteel
Nutteel Level 1 Level 1 (0 points)

From picture, i want the list of company as choices when i click company's name, it will appear in column C. Can anyone give a way to do this? Thank you for every answers.

make a list.png

  • 1. Re: make a list to choose
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Nuteel,

     

    Use Inspector > Cells.

     

    Select the cells in Column C (the rows *below* the Header Row "Company") and format them as Pop-up Menu.

     

    Default menu items will appear (mine were 1,2,3 and I could not change their names.)

     

    Click on "+" and edit the name of the new pop-up menu item (Sony or whatever). Repeat for Apple and Samsung.

     

    Delete ("-") any default menu items that you don't need.

    Screen Shot 2013-02-09 at 10.54.09 PM.png

    Now, when you select (click on) a body cell in column C, a Pop-up Menu (activated by the down arrow to the right of that cell) will allow you to choose an item.

     

    I hope this helps.

     

    Ian.

  • 2. Re: make a list to choose
    Nutteel Level 1 Level 1 (0 points)

    Thanks yellowbox, but i would like my list to be a choice because i have a lots of company's name. It will be very long list for me to put it all and choose only one of them for every cell (that's why i prefer company's name as a choice). thanks for your answer anyway.

  • 3. Re: make a list to choose
    Yellowbox Level 5 Level 5 (4,580 points)

    Hi Nutteel,

     

    Perhaps auto-completion will work for you.

     

    Page 79 0f the Numbers09 User Guide says this:

     

     

    Working with Text in Table Cells

    You can control the format and alignment of text in table cells, and you can use find- and-replace and spell-checking features.

    When you type text into a cell, Numbers displays text that might be used to complete the cell content based on similar text elsewhere in the table. You can use the suggested text if it’s appropriate, or you can keep typing to override suggestions. To disable auto-suggestions, deselect “Show auto-completion list in table columns” in the General pane of Numbers preferences.

     

    Please download the User Guide from the Help Menu in Numbers. It is very helpful.

     

    Regards,

     

    Ian.

     

    P.S. I don't use auto-completion. I find it annoying, like that paper-clip assistant in Microsoft Word!

  • 4. Re: make a list to choose
    HD Level 4 Level 4 (3,240 points)

    Not sure I understand exactly what you mean, but you can set up the list for one cell and then copy and paste it into other cells. The list is copied and pasted too.

  • 5. Re: make a list to choose
    Barry Level 7 Level 7 (29,180 points)

    Hi Nutteel,

     

    I don't think I understand your original request, either.

     

    You show a table listing the numbers 1 to 6 in cells B2 through B7, The label "Company" in cell C1, and three company names, Sony in G3, Apple in G4, and Samsung in G5.

     

    As I understand your question, you want to click on cell G3 and have "Sony" appear in one of the cells in column C. If you then click on G3 again, your expectation is that "Sony" would appear a second time in the next cell in column C.

     

    If that's a correct interpretation, you're out of luck.

     

    In a spreadsheet, the content of a cell (C2, for example) can depend on the current content of one or more specific cells. It cannot depend on a certain cell being clicked, unless that click also changes the content of the clicked cell.

     

    More than one cell (eg. C2, C4 and C6) can be set to depend on the content of the same cell or cells, but each of these cells will change if the content of the cell (or of any of the cells) which they depend on changes.

     

    Yellowbox has suggested one means of selecting a name in each of the cells in column C—using a pop-up menu placed in the cell itself.

     

    When creating this menu, I would suggest using a single space as the first menu item. This allows cells where a company has not yet been chosen to appear empty.

     

    Follow Ian's instructions (with the suggestion I added here) to create the pop-up menu in once cell, then Copy that cell and Paste it into the rest of the column.

     

    If you do want to select the name for each cell using a single click on another cell, here is an alternate method which will do that. The click cannot be on the cell containing the company name,though. It must be in a checkbox cell located in the column labeled with the Row number of the receiving cell, and in the same row as the Company name is listed on the selection table.

    Picture 4.png

    While the method works well, there is one thing I dislike about it. Each company can appear in as many (or as few) rows as chosen. Accomplishing that requires that, if the companied are listed in a column, each row on the Main table is controlled by a column of checkboxes on the Select table. That arrangement makes it necessary to edit the formula in each row of Main::Column C individually, rather than simply entering the first formula, then Filling it down the column.

     

    Here are the formulas:

    Main::C2: =OFFSET(Select :: $A$1,MATCH(TRUE,Select :: B,0)-1,0)

    Main::C3: =OFFSET(Select :: $A$1,MATCH(TRUE,Select :: C,0)-1,0)

     

    Note that the only change is in the column used in the Match function (B in C2, C in C3).

     

    Since the formula does not contain any Row references (except the base cell, $A$1, in which the row is locked), the formula in C2 may be filled down. then the column reference mentioned may be edited in each row to step one column to the right in each row.

     

    To eliminate the error triangle shown in Main::C10, add an error trap to C2 before filling down. After filling down, edit as described above.

     

    Main::C2: =IFERROR(OFFSET(Select :: $A$1,MATCH(TRUE,Select :: B,0)-1,0),"")

     

    The second table, "Select", uses a Footer Row (row 11) to count the checked boxes in each column and change the text in the cell depending on the result. The text, which can be hidden, is used in conjunction with a set of conditional formating rules the change the background colour in row 1 to green when a single checkbox has been checked and to red whne more than one has been checked. The rules must be edited for each cell.

     

    Select::B11: =IF(COUNTIF(B,TRUE)=1,B1,IF(COUNTIF(B,TRUE)>1,RIGHT(B1,3),"-"))

    Fill right.

     

    The conditional rules for cell Select::J1 are shown below the tables.

     

    Select B1-J1, following the model.

    Then select each of the cells in Row 1, and edit the rule to fit that cell. For B1, foe example, click on the first J11 lozenge, then click on cell B11 on the Select table. Repeat with the second Jii lozenge.

     

    Repeat the procedure for each cell to I11.

     

    Your table should now show results  similar to mine.

     

    Regards,

    Barry

  • 6. Re: make a list to choose
    Nutteel Level 1 Level 1 (0 points)

    You got it right,Barry. Thanks a lot. You are so cool with your explanation. Thanks a  lot