Auto populate another sheet when checkbox checked

There are no "rows" on Sheet 1. In Numbers, a Sheet is the large canvas on which the various objects (Tables, Text boxes, Charts, shapes and other graphics objects) are placed. A document may have one or more Sheets, and each sheet may contain zero or more of each of the types of objects listed above.

Rows (and columns) are found in Tables.

More details would be helpful in determining an answer specific to your situation.

Here are the assumed details: The first Table is named "Table 1", the second is named "Table 2" Both are on the same Sheet, "Sheet 1". The checkboxes are in column A of Table 1.

Table 1 has five columns; Table 2, which will not receive the TRUE or FALSE value in column A of Table 1, has four columns. where a checkbox is checked, all data on that row is transferred to the same row in Table 2.

Where the checkbox is unchecked, the cells in that row on Table 2 contain a null string, and appear 'blank'.

Formula: =IF(Table 1 :: $A,OFFSET(Table 1::$A$1,ROW(),COLUMN()),"")

Enter the formula into cell A2 of Table 2. Fill down the rest of column A, and right to column 4.

Regards,

Barry

C,

Refer to this graphic:

Note the addition of the AuxColumn in the top table. This is necessary to get the proper stuff transferred to the second table.

The AuxColumn expression is:

=IF(A, COUNTIF(A$2:A2, TRUE), "")

The expression in the second table is:

=IF(COUNTIF(Data :: $A, TRUE)>ROW()-2, INDEX(Data :: $A:$M, MATCH(ROW()-1, Data :: $M), COLUMN()+1), "")

The checkbox column is not copied down, as it has no value in the second table, and Checkboxes can't be programmed anyway.

Jerry

Hmmm...

Was about to post an update, but re-read your explanation.

I had based the transfer to Ad1 or Ad3 on the value in the Advisor column, as can be seen below:

Re-reading your description, I take it this is incorrect, and the correct interpretation is:

1. The entry is made on the Main table
2. IF "Done" is checked, the entry is copied to auxiliary table Ad1
3. IF both "Done" and "Paperwork" are checked, then entry is copied to auxiliary table Ad3

Questions:

• In situation 3 above, does the entry remain visible on Ad1, or only on Main and Ad3
• What is to happen if only "Paperwork" is checked?
• Is there any connection between the values in the Advisor column and the table to which the entry is to be copied?
• If so, how is the value (eg, Ad3) in the Advisor column determined? direct entry? formula?

Regards,

Barry

1. The entry is made on the Main table - CORRECT (this is the Master Everything table)
2. IF "Done" is checked, the entry is copied to auxiliary table Ad1 - CORRECT
   
3. IF both "Done" and "Paperwork" are checked, then entry is copied to auxiliary table Ad3 - NOT CORRECT. The Done is the only thing that will pull/sent the data to the auxiliary table Ad1. eg if paperwork is checked that will NOT pull/send the data to Ad1 ONLY a checked DONE can do that. (the reasoning is because Done signified all is complete so paperwork maybe recieved but payment might not be so the user will check DONE only when they know everything is complete)

OK. Sounds like the interpretation illustrated in the screenshot above (repeated here) was correct: The Done checkmark determines IF the data is transferred, and the Advisor column value (Ad1, Ad3, etc.) determines WHERE the data is transferred.

Questions:

Is there any connection between the values in the Advisor column and the table to which the entry is to be copied? - There is a direct relationship - Advisor 1 (Ad1) will only be sent to auxiliary table Ad1. Advisor 2 (Ad2) will only be sent to auxiliary table Ad2 and so on.... (other tables generated when needed)

That fits my earlier assumption, and the example tables posted above (using Ad1 and Ad3 as the breakout tables).

link to view what i have

Gets me to the iWork sign in page, and when I sign in, to a page offering to let me 'manage my documents,' but not to your document. iWork.com appears to require a more recent OS version thatn the one on this machine.

So, assuming the illustration above fits your needs, here's the peek into the back room an a rundown on what happens there:

The three yellow-filled columns contain indices to the checked ("Done") files assigned to each advisor (Ad1 through Ad3 shown—you will need one index column for each advisor).

The best place for these columns is to the right of all data in the Main table, where they will not lie between any columns whose data is to be transferred. The index columns would normally be hidden in the day-to-day use of the table(s).

"Done" checkboxes are in column A.

"Advisor" numbers (eg. Ad1) are in column G.

The index columns are H, I and J, and must be labeled (in Row 1) using the values shown (these must match the value used to identify each advisor in the Adv column).

Formulas for the index columns:

H2: =IF(AND($A=TRUE,$G=H$1),ROW(),99999)

I2: =IF(AND($A=TRUE,$G=I$1),ROW(),99999)

J2: =IF(AND($A=TRUE,$G=J$1),ROW(),99999)

Only the formula in H2 needs to be entered.

It can then be filled right for as many columns as there are advisors, and down to the bottom row of the table.

Note that your first index column will not be column H. The "H$1" cell reference should be changed to match the column containing the formula.

This reference to the label in the top cell of the column will adjust automatically to match the column as the formula is filled to the right.

$G is a fixed reference to column G. For your table, replace $G with $J, the column containing the Advisor information. The reference (and the one to $A) will remain the same as the formula is filled right.

The formula places the row number into each cell where the Done box has been checked on that row AND the Advisor value on that row matches the value at the top of the column containing the index. For cells where one or both of those conditions is not met,the formula places 99999 in the cell (The number is larger than the maximum number of rows in a table, and was chosen for that reason.)

Tables Ad1

This table contains one formula, entered in A2, and filled right and down from there.

A2: =IFERROR(OFFSET(Main :: $A$1,SMALL(Main :: $H,ROW()-1)-1,COLUMN()+1),"")

Working fom the inside:

• ROW()-1 returns the row number of the cell containing the formula (in A2, 2), then subtracts 1.
  The result (1) is handed to SMALL as its second argument.
• SMALL(Main::$H,1)-1 finds the 'first smallest' value in column H of Main (2), then subtracts 1, and hands the result (1) to OFFSET as its second argument.
• COLUMN() returns the column number of the cell containing the formula (in A2, 1), then adds 1. The result (2) is handed to OFFSET as its third argument.
  
• OFFSET(Main::$A$1,1,2) looks for and returns the value in the cell that is 1 row below and 2 columns ro the right of Main::$A$1, ("A") and places that value in the cell containing the formula (A2 on table Ad1).
  
• In A5 (of Ad1), SMALL finds the 'fourth smallest' number in column H, 99999 (and subtracts 1). When OFFSET looks for a cell that is 99997 rows below cell A1,that cell can't be found, and OFFSET throws an error.
  
• IFERROR catches the error, and places "" (the null string) in the cell (and in those below it).

The formula is filled across row 2 for as many columns as there are columns of data to transfer, and down for as many rows as will be necessary.

Table Ad3:

This table is a duplicate of Ad1, with a single change to the formula to use the index column for a different Advisor:

Ad1::A2: =IFERROR(OFFSET(Main :: $A$1,SMALL(Main :: $H,ROW()-1)-1,COLUMN()+1),"")

Ad3::A2: =IFERROR(OFFSET(Main :: $A$1,SMALL(Main :: $J,ROW()-1)-1,COLUMN()+1),"")

Regards,

Barry

Thanks for the detailed explanation! I had a very similar problem and implemented your solution, which worked beautifully except for one problem: Whenever I use that "categorize by this column" on the resulting table (Ad1 and Ad3 in the above example), it breaks, and I'm thinking it's because of the offsets?? What I mean by breaks is, the data in the resulting table is changed, and it appears categorized incorrectly.

For example, under the "cars" category, it contains "cars" and "trucks." Under the "trucks" header, it contains a few "trucks" and a few blanks (NULL?). And some of the data is missing. However, when I "delete categories", all my data comes back. Is this due to the new table being generated dynamically? Is there a solution to this problem? Thanks!!

I was able to insert before and after images of what happens when I try to categorize the destination table (the same as Ad1/Ad3 in the above example)... The "Reimbursable" field was the original checkbox on the source table that brought the rows over to the new tables (each "Property/Client" was a new table).

Hi Philip,

Off the top of my head, I'd say the OFFSET gets confused by the addition of the Category rows. If you need to use categories, I think the only way to do that may be to replace the formulas on the table with the results they calculated. To do that:

Click on any cell,

press command-A to select all,

Copy.

With the cells all still selected, go Edit > Paste Values.

Values in all cells will become fixed, and you should be able to apply Categories without issue.

Options: Select only the non-footer/ non-header rows, instead of All cells. This assumes the formulas in these rows do not reference the main table.

After doing this, the table will have to be repaired or replaced should you want to use it to gather iformation after changes in the main table. Replace is easiest, provided you keep an empty copy of this table which you can duplicate, then drag to the sheet where it's needed.

Haven't had time to check this. If there are problems, or further questions, post a reply, and I'll take a further look tomorrow.

Regards,

Barry

Thanks for the detailed (and super-fast) reply, Barry! Too bad this doesn't work "out of the box," but your solution gave me exactly the final result I needed. Well, BOTH of your solutions combined gave me exactly what I needed! I just duplicated the target tables so I can dynamically regenerate as necessary (hopefully just once each tax season), and copied/pasted as you recommended. Thanks a million for your help!

One observation regarding the offset functionality you devised in your first solution (above): If I categorize the source table, EVERY cell in the target tables gives the little blue triangle in the upper left corner with "the formula refers to cells without numbers." Whenever I delete categories on the source table, the little blue triangles on the target tables go away. I'm assuming therefore that this is due to the categories on the source table taking up a few rows. However, all the numbers on the target tables are correct, albeit with the blue triangles.

Regardless of whether or not the source table is using categories, the target tables require the copy/paste workaround of your second solution. I'm assuming this is just a limitation of the offset function combined with categories? Also, as much as I love your solution, it slows this spreadsheet to a snail's pace--even when adding/deleting categories on the target tables--presumably because of all the new calculations required to move source rows to target tables. Thanks again!

Hi Philip.

You've hit upon one of the things I think is a negative factor with numbers—it recalculates whenever there's a change to a table. I prefer the approach taken in AppleWorks, where it was possible to shut Autocalc off, then use Calculate Now to recalculate when you have finished entering new data.

Numbers is like a frantic duck—calm on the surface, but those little webbed feet are constantly churning away under the water!

Regards,

Barry

Hi Barry,

I'm trying to follow you explanation, but getting a bit lost. I want to auto-populate one table from another when the boxes are checked. Right now I have a crappy work around, an example with one product, but eventually I want to be able to select any of the options and have them autofill in the table above. This is to calculate food calories/protein etc.

Please see my attachments, maybe you have a simple solution?

Many thanks,

Alexander

Please let me know if you have a solution.

Hi Alexander,

Rather than moving data to a new table and then summing it, how about extracting the summary directly from the data table, something like this:

The formula in A2, filled right is: =SUMIF(Foods::$A,TRUE,Foods::C)

In your region you may need to use ; instead of , in the formula.

SG