Reference cell from another document

Tech,

I suggest trying to keep all the data in the same document (which is stored on your hard drive as a file).

In a Numbers document (show in screenshot above and titled "Untitled 37") there are several sheets (named "Summary", "Sheet 1", "Sheet 2", etc)

"Sheet 5" has a table that contains cells

You can store your data on one sheet and process/summarize the data in what ever way you want in other (or the same) sheet(s)

I suggest having a Numbers document for your budget that has a sheet for income, another for expenses and a summary.

This will remove the requirement to access another document

TheTechNut wrote:

Let's say I want to have the information in Document 1, Sheet 1, Table 1, Cell A2 added to Document 2, Sheet 1, Table 1, Cell A2. I

The AppleScript could be this:

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 (tgtCell'svalue) + (srcCell'svalue)

end tell

And the JavaScript for Automation could be this:

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();

In both cases you copy-paste the script into Script Editor (in Applications > Utilities) make sure Script Editor is checked at System Preferences > Security & Privacy > Privacy > Accessibility (this only needs to be done once) and also is toggled to the right scripting language, make sure both Numbers documents are open, and click the 'triangle' run button in Script Editor. If you end up with a script that you use often there are ways to have it in your menu and also attach it to a keyboard shortcut. Scripts can be an easy way to extend the functionality of Numbers, but note that they don't trigger updates automatically the way formulas do. You must trigger them yourself.

SG

"Is it possible to have a cell in one document reference a cell in another."

If the referencing document is a Numbers document, no.

Numbers cannot reference external documents other than to:

• Open a page on the web
• Open a new message in Mail, ready to be composed and sent.

AppleScript, or an Automator Service using an AppleScript may be able to open a separate document, collect a value from a specific location in that document, and place that value in a specific location in a Numbers document. If it is possible, the action would require user action to trigger. Details and confirmation would have to come from a source more familiar with AppleScript and what it can do, though.

Regards,

Barry

Yes, it's possible to reference another Numbers document via AppleScript or JavaScript for Automation (JAX).

But why go to that trouble when you can just keep your work within one document? A document can have a number of sheets, and each sheet can have multiple tables. That should be more than enough to work with.

SG

Numbers has documents, and documents have sheets, and sheets have tables, and tables have cells.

Scripts require precise terminology. To have a working example, you would need to start with providing that.

You mention that you want a cell in one document to change when an average changes in another document. That's not how scripts generally work. A script is not triggered by a change in a value in a cell. You have to trigger a script yourself.

SG

Can't see your script.

Also, did you go to System Preferences > Security & Privacy > Privacy > Accessibility and enable 'Script Editor.app'? Also, I think you may have to enable 'Numbers.app' there. (Tested here, and works well.)

SG

Maybe you need to do a restart to get the System Preferences settings to "take."

Or, I've noticed the .numbers extension doesn't show in your screenshot the way it does on my setup. You could try removing the .numbers from the script and see if that works on your setup. Or in Finder > Finder Preferences > Advanced try enabling 'Show all filename extensions.'

SG

Do you have a value in each of the A2's?

An error with 'missing value' suggests you've left either the source cell or target cell blank. There's no error-checking in these simple scripts. They expect a value in the cells.

SG