1 2 Previous Next 17 Replies Latest reply: Feb 21, 2013 1:27 AM by Barry
Elissa C Level 1 Level 1 (0 points)

In numbers, is there a way to use the column A cells from the table with color to create a drop down in a cell in the b&w table so that the data in the other cells (the watts @ STC for example) in the color table will populate into a third table? Input Page.jpgPT Enphase Screen Shot.jpg


Pages, Mac OS X (10.7.5)
  • 1. Re: Use cells from another table in a Drop Down Menu
    Wayne Contello Level 6 Level 6 (13,620 points)

    Not possible with Numbers without some fancy scripting.  You can provide feedback to Apple by selecting the menu item "Numbers > Provide Numbers Feedback"

  • 2. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    Thank you.    Can you help me with an If/Then on this?   If the cell says CSI then I need to see if the cell next to it says 245 or 250 and then populate the cell in another table with a field from the colored table above....Right now there are three manufacturer options and a total of 4 model options to populate.  I will need to get this one down because each option has six data fields that need the If/Then.

     

    I'm sure that my explanation is not as clear as it could be....do you have the option to chat?

     

    Many thanks!!

  • 3. Re: Use cells from another table in a Drop Down Menu
    Wayne Contello Level 6 Level 6 (13,620 points)

    Nope.   No clear to me.   

     

    the if function is simple by itself so let's start there:

     

    the function is is:

    i(<condition>, <return when true>, <return when false>)

     

    you can enter an if function in a cell by typing "=" then "if("

     

    after that you need to know what is the boolean condition you want to test... that's what an "if" is you cehck to see if some condition which can be true or false is true.

     

    so to see if a cell contains "CSI" you would enter:

    "=if(A1="CSI", "YES", "NO")

     

    this function would return "YES" when a cell contains the exact text "CSI" otherwise it returns "NO"

  • 4. Re: Use cells from another table in a Drop Down Menu
    Wayne Contello Level 6 Level 6 (13,620 points)

    Elissa,

     

    Please try stating exactly what you want rather than trying to say how you want us to solve the problem.  This may work better as I don't know what "the problem" is.  Also you must be very descriptive when refering to tables... for example saying:

     

    cell in another table with a field from the colored table above

    is ambiguous, to me, becuase both tables are colored and both are actually above the post.... or do you mean the top table in the image you posted.  You could be clearer indicating the "green" or "orange" table.

  • 5. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    Here goes:  in the Orange table are a list of manufacturers.  Each manufacturer has at least one option.  I need to be able to populate 1 cell in a table 4 (not shown) with data form column 3 (model number), then based on that model number I will need to populate 4 cells in table 4 with the data from the orange table in columns 5, 6, 7, 8 (Isc, Imp, Voc and Vmp)

     

    So my thinking so far with what I learned last Friday (I don't know how to insert "and")is this:  =IF(A1 =CSI and B1 =245, C1, IF(A1 =CSI and B1 =250, C1 etc...))   Is this getting close?

     

     

    Thanks so much!!!

     

    Elissa

  • 6. Re: Use cells from another table in a Drop Down Menu
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Elissa,

     

    The syntax for the AND function is:

     

    AND(test-expression, test-expression…)

     

    In your expression, you would write:

     

     

    =IF(AND(A1 =CSI, B1 =2450, C1, IF(AND(A1 =CSI,B1 =250), C1 etc...))

     

    You can find this answer yourself by opening the Function Browser. You can open it with View > Show Function Browser, or by clicking on a cell in the table then choosing Show Function Browser from the Function Icon on the Toolbar, or by clicking on the Function Browser Icon on the Format Bar after typing an equal sign in a cell.

     

    Screen Shot 2013-02-11 at 11.49.47 am.png

    Regards,

     

    Jerry

  • 7. Re: Use cells from another table in a Drop Down Menu
    Barry Level 7 Level 7 (29,180 points)

    Hi Elissa,

     

    This looks like a classic LOOKUP situation.

     

    Here's a skeleton view of the three tables involved.

    Table 1 is your input table, with  values selected in B3 and C3.

    Panels is your orange-striped table, with location labels (eg. C4) used in lieu of re-typing the actual data.

    Table 4 represents a set of cells in your Table 4, into which the requested data is rtransferred.

    Picture 20.png

    Panels is treated as a Lookup table, with the identifiers in column A as the search values.

     

    Table 4::A2 contains the formula below:

    =VLOOKUP(Table 1 :: $B3&" "&Table 1 :: $C3,Panels :: $A:$G,COLUMN()+2,0)

     

    Syntax: VLOOKUP(search-for,search-where,result-column,match type)

     

    Table 1 :: $B3&" "&Table 1 :: $C3 constructs the search-for string by concatenating the contents of Table 1::B3, a single space, and the contents of Table 1::C3. Result: "CSI 255"

     

    Panels :: $A:$G tell Numbers the lookup table is columns a through G of Panels, Numbers searches for "CSI 255" in the firt column of the Lookup table.

     

    COLUMN()+2   COLUMN() returns the number of the column containing the formula (1), +2 adds 2 to this value to tell the formula to return the value from the third column of the Lookup table (column C). As the formula is filled right, the result of Column increases, and the formula continues to return results from thecorrect column.

     

    0 (or FALSE) means don't accept a 'close-match. If the exact search-for value is not found, the formula will return an error message.

     

    To use the eror message, the whole formula is enclosed in and IFERROR statement:

     

    =IFERROR(VLOOKUP(Table 1 :: $B3&" "&Table 1 :: $C3,Panels :: $A:$G,COLUMN()+2,0),"---")

     

    The results of this version may be seen in Row 3 of Table 4, where the formula is looking for the values in B4 and C4 of Table 1.

     

    Regards,

    Barry

  • 8. Re: Use cells from another table in a Drop Down Menu
    Jerrold Green1 Level 7 Level 7 (28,995 points)

    Elissa,

     

     

    I just noticed that I left out a right parenthesis, just after the "2450". I should have written:

     

     

    In your expression, you would write:

     

    =IF(AND(A1 =CSI, B1 =2450), C1, IF(AND(A1 =CSI,B1 =250), C1 etc...))

     

    But, I think you got the gist of it.

     

    I wasn's sure what "CSI" means, but I figure you do. It should be either a cell address or a literal string, in quotes, or a value.

     

    Jerry

  • 9. Re: Use cells from another table in a Drop Down Menu
    Wayne Contello Level 6 Level 6 (13,620 points)

    I wasn's sure what "CSI" means, but I figure you do. It should be either a cell address or a literal string, in quotes, or a value.

     

    Crime Scene Investigation ( )... or this:

    http://www.dmsolar.com/csimosomo24p.html

  • 10. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    That made all the difference, Jerry....I had fortunatly figured it out.  I did look at the function dropdown and explanations, it was just a bit more complicated that was addressed in the numbers help and I had done a bit of research online as well.  I just had to build a framework to put the information in, having never used a function like this before.  Thank you for all your help!

  • 11. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    Haha, Wayne....yes, it's the abbreviation for Canadian Solar....I really appreciate all your help.  I got through the first one and then had constructed a good framework of understanding in my head so that I could move forward with the rest of them very quickly....Your help was integral to my understanding!

  • 12. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    Thanks Barry, since the original tables are preconstructed and are already layers and layers deep, using a lookup didn't seem like it would work in this situation...

  • 13. Re: Use cells from another table in a Drop Down Menu
    Elissa C Level 1 Level 1 (0 points)

    I am back to needing to use the VLookup function and can't figure it out....here's what I'm trying to do:

     

    Let's say there are 4 tables:

     

    1. Input

    2. Cover

    3. Data

    4. Output

     

    Table 1 has two options.  In Table 2 an option is chosen using a pop-up menu.  The data to go in cell A1 in table 4 will come from Table 3 --which data depends on which option is chosen in Table 1. 

     

    Any help on how to use lookup here would be most appreciated!

     

    Elissa

    -

  • 14. Re: Use cells from another table in a Drop Down Menu
    Barry Level 7 Level 7 (29,180 points)

    Hi Elissa,

     

    Your scenario seems pretty similar to that described in my earlier post.

     

     

    1. Input —> Table 1


    3. Data —> Panels is the data table.

    4. Output —> Table 4

     

    2. Cover —> no equivalent.

    (From your current description, Cover does not appear to be involved in the process of choosing which data to display.)

     

    Would a re-reading of that post lead to more specific questions?

     

    Regards,

    Barry

1 2 Previous Next