Updating values between 2 different spreadsheets by 2 different users

[chinnychinchin]

Hi all, first time poster here. Hoping someone can help.

I've created 2 spreadsheets to use in a mock scenario for testing my department's flood response procedures. On the 1st spreadsheet (called Location), I've written a macro to constantly increase the value of the river levels every minute. The 2nd spreadsheet (called Overview) then has links to cell values and has an automatically updating chart based on values from the Location spreadsheet. When I have both spreadsheets open on the same laptop, the Overview spreadsheet automatically updates the values every time the values increase on the Location spreadsheet. But when I have the spreadsheets open on different computers, the values do not automatically update. For the scenario, I will need to have the spreadsheets open on different computers as staff will be based in different offices. Both spreadsheets are on our shared network drive.

I have tried sharing the Location spreadsheet, and having this open on both computers. This does work, but it can only update every 5 minutes, which is not often enough, and it also messes up the macros on the spreadsheet.

Does anyone have any suggestions? I'm using Excel 2013.

Thanks

 

[Drrellik]

if you are able to leave both workbooks in the same location folder and give permission for the other locations to share you might get better results, just a thought.