Creating multiple tables from a single table in Numbers '09

I assume you meant that the next set of data starts in the next column but I'm not sure.

I think the solution will be to create a new table that uses formulas such as =Table 1:: A1 to bring over the data you want for each person. This table will be like your original table, it will have all the columns of names and their data (less the stuff you don't want). After you have done that, you can lift the individual columns out of that table and make new tables from them. Just grab a column letter, drag the column up and out of the table, and place it on an empty spot on the canvas. These tables will all be linked to the original table. Changes made to the original table will be reflected in the other tables. If you don't want it that way, before you start dragging the columns out of the table, select all of the cells and do a Copy/Paste Values back to the same place. This will replace the formulas with the values.

Hi Charles,

I'm going to assume that replacing Tom's residence/place of birth in A2 with Bill's name was a typo, and that Bill's name actually goes in B1, with the rest of his data listed below.

This arrangement is contrary to the expected arrangement of Numbers tables, which expects each ROW to be a record, and each COLUMN to contain data from a single field in that record. Unless you're planning to sort or filter it by categories (both of which reorganize the rows based on the contents of one or more columns), keeping the data arranged this way is probably a minor issue.

What follows requires that each of those names must be unique within the table—you cannot have two people identified as John Jones, but you can have one listed as John Jones and a second listed as John J Jones, or, using first names only, you could have a Bill, a William, a Will and a Billy.

Here's a small example, using the supplied sample data to produce separate tables for Tom and Bill.


Main is the main data table.

Tom is the subsidiary table to contain some of Tom's data. The table name is a convenient way of locating the table, and does not enter into the calculations.

A2 and A3 of this table contain the formulas below, identical except for the return row argument.

A2: =HLOOKUP($A$1,Main :: $A:$B,3,FALSE)
A3: =HLOOKUP($A$1,Main :: $A:$B,4,FALSE)

(FALSE will display as "exact match")

Enter "Tom" (without the quotes) into A1, and this table will be populated with the data in rows 3 and 4 of Tom's column in the table Main.

The table Bill is a duplicate of Tom. Click Tom's table icon in the sheets list, press command-D, then, if desired, rename the copy of the table. Replace "Tom" in A1 of this table with "Bill", and it will be populated with the selected data from Bill's column on Main.



This is a pretty bare-bones solution, requiring that the formulas be individually edited to set the row from which each piece of data is collected. It is fairly easy to use check boxes (or some other marker) to choose which rows are included in (all of) the subsidiary tables (instead of specifying the row directly in the individual formulas), and we can pursue that if you'd find it useful.

Regards,
Barry

HLOOKUP is discussed on p 211 of the iWork Formulas and Functions User Guide, which you can download through the Help menu in Numbers.
B