how to add a function that sums the same …
apple Verified ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Hi everybody, I need to create invoices with this software. Each invoice is a new sheet.
I would like to have the possibility to automatically sum values written in the same cell of each sheet I create.
How can I do?
thanks
7 replies
Sign in or join Beachball Help to reply.
- 0
Barry ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Your formula will look like this:
Assuming the sheets and tables have the default names assigned by Numbers as they are created, that the Invoice is the only table on each sheet, and the the values to be summed are in cell D6 of each table, your formula will look like this:
SUM(Sheet 1::Table 1::D6,Sheet 2::Table 1::D6,Sheet 3::Table 1::D6,Sheet 4::Table 1::D6,Sheet 5::Table 1::D6,Sheet 6::Table 1::D6)
As written, the formula will calculate the sum of the b=values in D6 of the table (Invoice) on Sheets 1 through 6. For each sheet that is added to the document, you will need to add ",Sheet n::Table 1::D6"(where 'n' i the number of the next Sheet) to the comma separated list in the formula.
As should be clear, this becomes unwieldy pretty quickly.
The spreadsheet-friendlier approach is to dedicate a single table to recording all transactions, each identified with an invoice number, then create one invoice table (on a separate sheet) to extract the information for each invoice from the transactions table, to be printed (to paper or as a pdf document) and sent to the customer.
Regards,
Barry
- 0
Barry ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Your formula will look like this:
Assuming the sheets and tables have the default names assigned by Numbers as they are created, that the Invoice is the only table on each sheet, and the the values to be summed are in cell D6 of each table, your formula will look like this:
SUM(Sheet 1::Table 1::D6,Sheet 2::Table 1::D6,Sheet 3::Table 1::D6,Sheet 4::Table 1::D6,Sheet 5::Table 1::D6,Sheet 6::Table 1::D6)
As written, the formula will calculate the sum of the b=values in D6 of the table (Invoice) on Sheets 1 through 6. For each sheet that is added to the document, you will need to add ",Sheet n::Table 1::D6"(where 'n' i the number of the next Sheet) to the comma separated list in the formula.
As should be clear, this becomes unwieldy pretty quickly.
The spreadsheet-friendlier approach is to dedicate a single table to recording all transactions, each identified with an invoice number, then create one invoice table (on a separate sheet) to extract the information for each invoice from the transactions table, to be printed (to paper or as a pdf document) and sent to the customer.
Regards,
Barry
- 0
D.N.Ale ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
First of all thanks for being so fast and clear.
I have a question about your answer.
assuming that the values, the dates and the invoices number are in a table with the same name and in three cells (for example D4, D5, D6) that remains the same for every different sheets, how can I record all the transactions, invoices number and dates automatically in one sheet?
sorry for my English
- 0
Wayne Contello ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Assuming the sheet names can be put in a list, then you can summarize the total of the same cell in those sheet by doing something like this:
Name the sheets and the table on each Sheet. Use the same name for the table on each sheet (I used the table name "Data")
Here is an example:
Sheet named "Invoice 1" table named "Data"
Sheet named "Invoice 2" table named "Data"
Sheet named "Invoice 3" table named "Data"
Here is a summary of these invoices:
Add the Sheet name in the column A then in column B use the formula in cell B3:
=INDIRECT($A3&"::Data::B5")
shorthand is:
B3=INDIRECT($A3&"::Data::B5")
select cell 3, copy
select cells B3 thru B5 (or as needed), paste
- 0
D.N.Ale ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
Great!!!
this works!!!!
the only thing I have to do is to remember to write the sheet name and to copy/paste the function you teached me.
Thank you very much
- 0
Sheppad ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
You know, the only thing I might add, is that if you ever add rows into your table, your summing can get screwed up, because your row references can then be off.
- 0
D.N.Ale ·
Imported from the Apple Support Forums via the Wayback Machine 🔗
I'll be careful.
thanks once again