I have a table structure like the following;
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-OTHER, OS X Mountain Lion (10.8.4)
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
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
Thanks Barry.
Here's my final query For Table 2 B2 (First/Data);
=SUM(OFFSET(Table 1 :: A2,0,(MATCH(TRUE,Table 1::5:5,0)-1)))
Then for the subsequent rows I changed the Row value;
=SUM(OFFSET(Table 1 :: A2,0,(MATCH(TRUE,Table 1::5:5,0)-1)))
=SUM(OFFSET(Table 1 :: A2,1,(MATCH(TRUE,Table 1::5:5,0)-1)))
=SUM(OFFSET(Table 1 :: A2,2,(MATCH(TRUE,Table 1::5:5,0)-1)))
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
Thanks Barry.
Have removed the SUM formula and extra brackets.
I was wondering why my formulas would not automatically update when I filled down a column. Have corrected my mistake and I'm now using the 'relative' & 'absolute' options. What a time saver.
Thanks for the tip!
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
Making a check box load column data into another table
