Skip navigation

Making a check box load column data into another table

220 Views 5 Replies Latest reply: Sep 1, 2013 10:47 PM by Barry RSS
Ward6 Calculating status...
Currently Being Moderated
Sep 1, 2013 5:16 PM

I have a table structure like the following;

numbers_screenshot.png

 

I want to be able to select 'Basic', 'Intermediate' or 'Advanced' via a check box at the bottom of it's column in Table 1 and have that data populate Table 2. I can work on the individual formulas for 'COLUMN' 'LOOKUP', 'IF', 'COUNTIF' & 'OFFSET' but can't seem to string them together to achive this.

Numbers, OS X Mountain Lion (10.8.4)
  • Barry Level 7 Level 7 (29,095 points)

    Hi Ward6,

     

    Try using MATCH(TRUE,Table 1::5:5,0)-1

     

    Fro the example shown, Match will return a 2. The -1 makes that a 1, the column offset you need from Table 1;;$A$1 for column B.

     

    Regards,

    Barry

     

    (Not checked, as no time right now.)

    B

  • Barry Level 7 Level 7 (29,095 points)

    Hi Ward6,

     

    Looks good. A couple of comments for future reference:

     

    There's no need for SUM in these formulas; each is returning a single numerical value.

     

    Numbers will automatically update cell references in formulas as you fill formulas down a column (or across a row). Rather than changing the row, you could write the formula once (for row 2), using an 'absolute start and end' reference for Row 5' (see below), then fill it down to rows 3 and 4. Numbers would update the A2 reference for each row, but leave the absolute reference to row 5 ($5:$5) as is. You can set that refeerence to 'absolute start and end' using the popup menu attached to the lozenge in which the reference is displayed in the Formula Editor and the Entry Bar. Hover the moouse over the lozenge, then click the white triangle that appears toward its right end. Choose 'absolute start and end.'

     

    Here's what the three formulas would look like with those changes. Remember, only the formula for B2 was written. Numbers made the change in cell reference as that formula was filled into B3 and B4 by dragging the Fill Handle at the botom right corner of the selected cell (B2).

     

    T2::B2: =OFFSET(Table 1 :: A2,0,(MATCH(TRUE,Table 1::$5:$5,0)-1))

    T2::B3: =OFFSET(Table 1 :: A3,0,(MATCH(TRUE,Table 1::$5:$5,0)-1))

    T2::B4: =OFFSET(Table 1 :: A4,0,(MATCH(TRUE,Table 1::$5:$5,0)-1))

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,095 points)
    Currently Being Moderated
    Sep 1, 2013 10:47 PM (in response to Ward6)

    One other thng regarding OFFSET:

     

    I usually use a fixed reference to cell A1 on the table from which the data is being retrieved. If row 1 is a Header Row, that makes the formula immune to sorting of the source table. Row and column offsets can still be automated by using the ROW() and COLUMN() functions (or MATCH) to set them. Not terrificly useful in the example here, but can be in many other situations.

     

    Using a fixed base and ROW() to set the row offset, the formula in B2 of Table 2 would be:

     

    T2::B2: =OFFSET(Table 1 :: $A$1,ROW()-1,MATCH(TRUE,Table 1::$5:$5,0)-1)

     

    Filled down, you'd see no change in the formula, but the actual down offset would change with each row.

     

    Regards,

    Barry

     

    PS: Noticed and removed another pair of redundant parentheses from this.

    B

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.