Referencing multiple tables in Numbers

Moose013 wrote:

I don't want to do this with 17 tables and counting.

Since the data in each table is the same structure you might consider consolidating them into one, then extracting summaries you need with simple, short formulas, something like this.

In the Results table you have an added column to show the game number. You can filter on that when you want to show the results of just one game.

If you set up and enter your data this way, then the formulas become short and simple:

In my example:

In B2, filled down:

= SUMIF(Results::B,A2,Results::C)

In C2, filled down:

=COUNTIF(Results::B,A2)

In D2, filled down:

=B2/C2

That's much more manageable than having multiple tables and then trying pull things together with huge long formulas.

SG

Hi Moose,

You have discovered the folly of recording the data on several tables, then attempting to gather data from all those to summarize.

Fortunately, the UNION.RANGES, introduced in Numbers 5, can save you some of the work.

The image shows COUNTIF as it appears in B2 of the table Main.

UNION.RANGES is used to combine the ranges, each consisting of all of column A in one of the five data tables into a single range of cells in which to count the occurrences of the name in A2 of the Main table.

Main::B2: COUNTIF(UNION.RANGES(FALSE,Table 1::A,Table 2::A,Table 3::A,Table 4::A,Table 5::A),A2)

The formula is filled down to the last row in Main containing a name.

Regards,

Barry

"That's much more manageable than having multiple tables and then trying pull things together with huge long formulas."

Agreed. Hence my "folly" remark.

Regards,

Barry