how to use cells from many files

Hi Frederic,

Numbers has never linked between files (documents) without using AppleScript.

My method is to include all data in one document. Tables will link to other tables, and between different Sheets (Tabs).

Please reply with your overall aim.

Regards,

Ian.

Hi Fredericplat,

"How can i use cells from different files on the same sheet with numbers…"

Some Numbers vocabulary might be useful in clarifying your question:

Document, Spreadsheet: Both are short for "Spreadsheet Document"

When you open a Numbers file, you are viewing or working on or with a Numbers Spreadsheet Document.

Sheet: Every Document contains at least one Sheet—a blank 'canvas' on which you may place Tables, Charts, Images, Shapes, Text boxes, and other Objects. Every Sheet in a Document must have a distinct name. When Sheets are added, the default name is "Sheet " followed by the lowest serial number available.

Table: A collection of Cells, arranged in a rectangular array of rows and columns. Each Sheet, when created, comes with one Table, named "Table 1". Other Tables may be added to the sheet using the Table button. Every Table on the same Sheet must have a distinct name. When tables are added to a Sheet using the Table button, each is named "Table " followed by the lowest available serial number. Tables on different Sheets in the same document may have the same name, but there are advantages to renaming Tables to avoid duplicate Table names elsewhere in the same Document.

Cell: Cells contain data that has been entered or inserted, or they contain data generated by a formula contained in the cell. They cannot contain both. To get data from a cell, a formula must reference that cell or a range of cells including that cell.

A cell is referenced by its address. A formula that is on the same Table as the referenced cell needs only the column letter and the row number of the referenced cell.

To get data from a Cell in a Table that is not on the same Table, the formula requires the full address of the cell (or enough of that address for Numbers to locate that specific cell).

The full address of the top left cell on Table 1 on Sheet 3 is: Sheet 3::Table 1::A1

But if that "Table 1" (one of at least three tables with that name if everything still has its original name) is given a new name, and the new name is distinct (ie. different from the name of any other Table in the document), then Numbers is able to locate that table (and that cell) without using the sheet name, and the sheet name may be omitted from the cell address. In fact, if it is included under those circumstances, Numbers will drop it.

So the answer to your question depends on the meaning of your terms. Can you clarify?

Regards,

Barry

Numbers cannot, at this time, refer to any cell in a different document. You can provide feedback to Apple using the Numbers menu item "Numbers > Provide Numbers Feedback"

You can also consider using MS Excel, or an Office clone if this feature is essential

Hi Frederic,

Can't be done directly. Numbers formulas can reference only cells that are in the same document.

You can use an AppleScript to collect data from one or more cells on a single table, and to place that data on the Clipboard, then Paste it into one or more cells on a table in a second document.

Such a script might be expandable to perform both the collecting and inserting operations, and to switch files and locate the specific tables and cells for each operation, but I don't recall having seen that actually done.

There are a few skilled scripters visiting this community (some regularly, others ocasionally), and one or more of them may be able to see a solution here.

Scripts tend to be pretty literal in what they need to know, so do expect some questions.

Regards,

Barry

Fredericplat wrote:

Hi Barry.

I would like to use a cell from a table from a sheet from a file, in an cell from a table from a sheet from a different file.

Both file are on the same device.

Since it is so convenient to have multiple tables on the same sheet in Numbers it is usually more efficient to keep your work together in one document. However, if that is not possible for some reason, then accessing data from a separate document usually only requires a short script.

AppleScript example:

tell application "Numbers"

set srcCell to document "MyTestDoc1.numbers"'s sheet 1's table 1's cell "A2"

set tgtCell to document "MyTestDoc2.numbers"'s sheet 1's table 1's cell "A2"

set tgtCell'svalue to srcCell'svalue

end tell

JavaScript for Automation example:

Numbers = Application('Numbers');

var srcCell = Numbers.documents['MyTestDoc1.numbers'].sheets[0].tables[0].cells['A2'];

var tgtCell = Numbers.documents['MyTestDoc2.numbers'].sheets[0].tables[0].cells['A2'];

tgtCell.value = tgtCell.value()+srcCell.value();

SG