I need to use numbers to compile a spreadsheet using 10 separate spreadsheets and it must update as the spreadsheets update

If the spreadsheets are in different files, then Numbers cannot meet your requirements.

If, however, the 10 different spreadsheets are in the different tables (of sheets) of the same file, then this is doable.

Because of the statement "All the spreadsheets are stored in different places on a server." I think you should use Excel, Libre Office or some other application.

cemap24 wrote:

At the moment I copy each of the 10 spreadsheets and then paste them into my new sheet.

I need to be able to link the cells from the 10 source spreadsheets so they update my combined spreadsheets.

All the spreadsheets are stored in different places on a server.

Is this something that Numbers can do easily or will i have to change to Excel

This doesn't sound like something that is all that easy to do with Excel, either. The links will break if one of the workbooks gets moved on the server, and then you have to hunt through the formulas to revise them.

In Numbers consolidating information from different documents into one document can be done without too much trouble using AppleScript. The disadvantage: you have to click to run the script to update the numbers; unlike formulas, changes are not updated entirely automatically. An advantage: the source documents and ranges you are pulling your data from are explicit in the script, thus easier to change when something changes on the server.

If you give more specifics (what kinds of values are you consolidating; one cell or a range, etc) we can post some suggestions. This sounds like the kind of thing that is well worth spending a little time trying to automate.

SG

Below is a simple script that copies values from two different "source" documents and consolidates them in a "target" document.

It would be easy to extend this to 10 documents simply by adding more property pairs and copyVals() lines.

To try it out, copy-paste into Script Editor (in Applications > Utilities), set up documents with names matching the script (or change the script to match your setup, by editing the values in black between the ""), and, with the documents open, click the triangle 'run' button.

There is minimal error checking so you'll want to make sure the range sizes are the same for the source and target in each property pair.

SG

property src1 : {doc:"Source1.numbers", sht:"Sheet 1", tbl:"Table 1", rng:"A2:B4"}

property tgt1 : {doc:"Target.numbers", sht:"Sheet 1", tbl:"Consolidated", rng:"A2:B4"}

property src2 : {doc:"Source2.numbers", sht:"Sheet 1", tbl:"Table 1", rng:"A2:B4"}

property tgt2 : {doc:"Target.numbers", sht:"Sheet 1", tbl:"Consolidated", rng:"A5:B7"}

copyVals(src1, tgt1)

copyVals(src2, tgt2)

to copyVals(src, tgt)

tell application "Numbers"

try

--read values in source range into AppleScript list

tell document (src's doc)

tell sheet (src's sht)

tell table (src's tbl)

tell range (src's rng)

set vv to cells's value

end tell

end tell

end tell

end tell

--write values from list to target range

tell document (tgt's doc)

tell sheet (tgt's sht)

tell table (tgt's tbl)

tell range (tgt's rng)

repeat with i from 1 to count cells

set cell i's value to vv's item i

end repeat

end tell

end tell

end tell

end tell

on error

display alert "Check addresses, ranges, and size of destination table" buttons "Ok"

end try

end tell

end copyVals