How do I add cells from several tables within a sheet?

Question

Level 1

5 points

How do I add cells from several tables within a sheet?

I know my question sounds basic but I need help anyway.

I have a sheet called "At a Glance" that has many tables (shown below). The tables in "At a Glance" are labled Checkpoint Truck 1 thru Checkpoint Truck 30. Each table is identical to the other aside fron the name. Each table represents a month showing data from days 1-31.

I have a control sheet called "Command Central" that has a admin table identical in format to the tables in "At a Glance". The admin table's purpose is to count the names in Row 3 and to show the SUM of other cells (shown below). Ignore Row 4, it will be deleted.

If possible can a SUM INDIRECT formula be used to count and add the data from cells within multiple tables?

iMac (27-inch, Late 2012), OS X Mavericks (10.9.2)

Posted on Apr 11, 2014 12:21 AM

Reply

Answer 1

Apr 11, 2014 6:16 AM in response to blkrocket

The expression would have the form: =SUM(range1, range2, range3)

range1, etc., may be single cells or ranges of cells.

Jerry

Reply

Answer 2

blkrocket Author

Level 1

5 points

Apr 12, 2014 3:49 PM in response to Jerrold Green1

Im not sure what you mean by range. What formula can I use that will SUM a cell (D5) from multple tables within a sheet? I have been using a SUM INDIRECT to retrieve the value of a single cell and copy it to a cell on another page but I would not like to retieve the value of a cell from multiple tables, add those cells together and place that value in a cell on another sheet. The formula I was using is:

D5=IF(LEN(INDIRECT("Truck 1::Day "&COLUMN(cell)−3&"::K38",addr-style))<1,"",INDIRECT("Truck 1::Day "&COLUMN(cell)−3&"::K38",addr-style))

I magine the new formula to SUM D5 from multiple tables to look like:

D5=SUM(INDIRECT("At a Glance::Checkpoint Truck "&COLUMN(cell)-3&"::D5"))

When I use this formula I get a Invalid refference error. What am I doing wrong?

Reply

Answer 3

Apr 12, 2014 7:53 PM in response to blkrocket

A Range is one or more contiguous cells in a Row, Column or rectangle.

There is no way to reference to the same cell in multiple tables without mentioning the individual tables separately.

In your example, you are quoting the syntax description literally without substituting the required parameters.

Jerry

Reply

Answer 4

blkrocket Author

Level 1

5 points

Apr 12, 2014 10:27 PM in response to Jerrold Green1

I am wanting to avoid writing a long formula that sums cell from 30+ tables and growing. What formula could I use that would not require doing so?

Reply

Answer 5

Barry

Level 9

59,867 points

Apr 13, 2014 2:29 AM in response to blkrocket

Hi br,

See Jerry's post.

A Range can be specified only for contiguous cells.

Example: SUM(Sheet 1::Table 1::B2:AF2) will return the SUM of the numerical values in the 31 contiguous cells, B2 to AF2)

Non-contiguous cells must be specified as a list.

Example: SUM(B2,C3,D2,E3,F4,G5,H11,I2,J5) will return the sum of the numerical values in the nine cells (on the same table as the formula) named in the list.

Cells that are on different tables are NOT contiguous. Cells that are on tables other than the one containing the formula must be identified with their complete address (Sheetname::Tablename::A1). If the Tablename is unique within the document, the Sheetname may be omitted.

For your described situation (single cells on 30+ tables), you will need to identify each cell with its full address (as has been done in the formulas supplied for earier versions of this question). You have limited means of shortening these formulas.

Ensure that your Table names are each unique within the document, and that they are as short as possible. For this set of tables, the shortest possible is likely T1, T2, T3, etc.
Put the cell addresses directly into the formula, rather than constucting each with INDIRECT. This will make the formulas shorter, but will also make them more error prone, as they will require individual editing.

Regards,

Barry

Reply