Help us share our data

[missmerredew]

As the data co ordinator for a science course, I now have a large spreadsheet that a number of colleagues need to access and change data - potentially at the same time.
Though we are all on the same network, once or twice the sheet has been opened and modified at the same time as another is using it and the changes have been lost. (no read only message - for some reason)
As an attempt to stop that, we have tried sharing the sheet, but now cannot update the pivot tables we use to motivate pupils.
Is there any way of sharing/updating this data without using the shared workbook option?

 

[nbrcrunch]

this may or not work. I've done similar things using separate workbooks, one for each auditor/editor. Then us MS-Query (built into Excel) to build a single master. I was just today reading a technical document on Microsoft's website about that very matter. I didn't keep the link. (I don't know why, but it recommended involving MS-Word's Mail Merge as an intermediary. That part didn't make any sense to me so I closed the reference.)

Addendum:
http://office.microsoft.com/en-us/excel/HP052747511033.aspx
http://office.microsoft.com/en-us/access/HA011860631033.aspx
http://www.exceluser.com/explore/msquery1_1.htm

 

[missmerredew]

Thankyou for that - looks like ive got some in depth reading ahead of me.

 

[lenze]

Are you actually sharing the WorkBook (Tools>Share WorkBook)? If so, choose allow editing by more than 1 user at a time. On the advanced tab, you can select hwo changes are saved.(ie Set a priority)
HTH
lenze