HI Eaglegrl,
Here's an example showing it is possible.
There are three formulas on the Summary table.
The core of the formula pulling data from the six small tables to the corresponding cells of the Summary table is this:
=INDIRECT("'"&B$1&"'::B"&ROW())
This is the formula as it would look in cell B2 of Summary. INDIRECT constructs the cell address 'Jan-Feb'::B2 to tell Numbers to put the data from cell B2 on the table named Jan-Feb into this cell.
All is well until the formula gets to column G, where Indirect will construct the address 'Nov-Dec'::B2. That cell is empty, and Numbers will interpret that as a value of zero. If you are interested only in the Total for the year, that's not a problem. But if you are calculating the average, AVERAGE will include the zero in its calculations and return a result that is too low. To prevent that, the full formula adds a test that measures the length of the entry in the target cell. If an entry has been made, it will be at least one character long, and IF will return the value from the cell. If no entry has been made, its length will be zero, and IF will return a null string—a piece of 'text' that has zero length. AVERAGE will ignore text, and return the correct average.
Full formula:
B2: =IF(LEN(INDIRECT("'"&B$1&"'::B"&ROW()))>0,INDIRECT("'"&B$1&"'::B"&ROW()),"")
Filled down to B5; Filled right to column G
Total
H2: =SUM(B2:G2)
Filled down to H5
Average:
I2: =AVERAGE(B2:G2)
Filled down to I5 ('eye-five')
BUT
Just because it can be done doesn't make it the best way to accomplish what you want.
I would strongly suggest that you take a close look at the Personal Budget template supplied with Numbers 3, or the similar Checking Register template supplied with Numbers '09. These use a single table on which to record expenses as they occur, and one or more Summary tables to extract the category totals.
Also take a look at the suggestions in this discussion of a similar question.
Regards,
Barry