How to make a table in Numbers with data from multiple invoices?

Hi AldoTin,

Adding to Barry's reply, this idea may move the discussion forward. The INDIRECT function is your friend. I used simple invoice layouts. If your invoices are more complex, we must revise to suit.

In this example, all invoices and the Summary table are on one Sheet. That makes it easier to create references in formulas. Each invoice table is named with the invoice number (so that INDIRECT can find a specific invoice table). Because each invoice table has a unique name, invoices can be on different Sheets.

Formula in Summary B2 =INDIRECT($A2&"::"&"B2")

That creates an address for INDIRECT to find.

We need to edit that formula after we fill across. In this example,

Formula in Summary C2 =INDIRECT($A2&"::"&"C2")

Formula in Summary D2 =INDIRECT($A2&"::"&"D2")

Formula in Summary E2 =INDIRECT($A2&"::"&"E2")

Formula in Summary F2 =INDIRECT($A2&"::"&"F2")

Or, wherever those values occur in an invoice.

I look forward to replies from you (showing the layout of your invoices) and comments from other users.

I agree with Barry. "Pulling" data from a master table into individual invoices is easier than gathering data from existing invoices.

Regards,

Ian.

Essentially, you are looking for a document that has a form on which you enter data, and a Table onto which that data is transferred.

Forms (fairly simple ones) are supported in the iOS version of Numbers, where screen space is very cramped when conpared to the Mac. I've not seen anything indicating that forms have been added to the macOS version. Mine is an older version, so I may not be up to date on the features currently available in Numbers.

On the Mac, you'd be working with formulas to transfer the data from the invoice (essentially a data entry table) to the larger 'master' table.

Formulas can't 'push' data to another location. They can 'pull' data from another location to the cell occupied with the formula.

That means that to transfer the data in the direction you want, you would need an invoice table for EACH transaction (which could include several items).

You'd also need:

• A large table with one row for each invoice.
• A formula in each cell of a row of the large table to get information from one cell on the invoice assigned to this row.
• A copy of each of those formulas in the same columns of every row of the large table, each edited to get the data from the same cell on a different table. (This is not a trivial task.)

Reversing the direction of flow of the data:

Record your data on the 'master' table. Use a set of cells on the Invoice table to collect the information from the correct row of the master table, then print the invoice as a pdf file to send to the customer and to save for your 'paper copy' of the transaction.

If you want to keep the computer version of the invoice, you'll need a copy of the invoice for each transaction recorded in a row on the large table, In the single row on the master table, or a pdf copy of the invoice is a satisfactory record of the transaction, you'll need only as many copies if the invoice as you will have open at the same time. Changing the content of the invoice (to the content describing another already recorded transaction) would be accomplished by changing the invoice number in a single cell.

Anyone joining the discussion and wanting to work on a solution will benefit from more information:

What version of macOS or OS X is running on your Mac? (Apple menu, About this Mac. The version number will be under the Apple logo on the small window that opens.

What version of Numbers? (Numbers menu > About Numbers)

What data will be recorded for each sale? (List in the order these will appear across a row in the large table)

Invoice Number should be listed in the first column.

What data will be shown on the invoice?

What will the invoice look like?

The main part of the invoice will be a table. Information that does not change (Your company name and address, etc.) can be placed in a table or in one or more text boxes.

Regards,

Barry

Hi AldoTin,

Thanks for the extended reply. It's always great to read "However, thanks to your replies, I have been able to find a simple solution for my needs." and other words to that effect,

Any concerns regarding the error triangles on the unused lines of the Invoice Data table?

Regards,

Barry

Yes.

Wrap the current formula in an IF statement that suppresses calculation until there is something entered in that row of column A:

B2: IF($A2="","",your formula)

The $ locks the column A reference on that column.

"" (two quotation marks with nothing between them) is a null string—a text value with zero length. Used as a search term, it will return TRUE for an empty cell. Inserted by a formula, it will give the appearance of an empty cell.

your formula means the formula currently in that cell.

Regards,

Barry