View only, Live Connection

Hello Ismail,

Apologies for the delayed response.

It seems like you're encountering an issue where the data from Workbook A isn't refreshing automatically when someone with "view-only" access opens Workbook B. Based on the scenario you've described, the issue likely arises from how Excel handles external data connections and the permissions associated with them.

Why this is happening:
External Links and Refresh Behaviour: When you use the = sign to link cells from Workbook A to Workbook B (essentially creating an external reference), Excel automatically tries to update that reference when Workbook B is opened. However, if Workbook B is set to "View Only" and someone opens it without permission to edit, Excel doesn't automatically refresh the links. This is because Excel considers refreshing the data as an action that could affect the workbook's content and therefore requires editing permissions.

Permissions and Data Connections:

Edit Permissions: If a user has edit permissions, Excel allows the refresh because it assumes the user is authorized to make changes to the data and the workbook's content.
View-Only Permissions: For users with view-only permissions, Excel restricts any action that could alter the workbook's content. Refreshing the data from an external source is considered an action that alters the data, so it requires edit permissions to be executed.
Refresh Triggers: Excel typically requires an explicit action (like opening the file, pressing a refresh button, or enabling automatic refresh) to update external links. Users with "View Only" access may not trigger this refresh when opening the workbook because Excel doesn’t allow them to perform actions that modify or refresh the content.

Possible Solutions:
Use "Data Connections" Instead of Direct Links: Instead of manually linking cells with =, try setting up a data connection to Workbook A. This is a more structured way of linking data between workbooks. Here’s how you can do it:

Open Workbook B.
Go to the Data tab.
Click on Get Data (or From Workbook under "Get External Data").
Choose From Workbook and browse to Workbook A.
Select the data range you want to link, and choose how you want the data to be imported (like a table or a range).
Data connections are more reliable for refreshing external data and can be configured to refresh automatically or manually.

Automatic Refresh Settings: If you set up a data connection, you can configure it to refresh automatically when the file is opened. This can be done under Data Connection Properties:

Go to the Data tab.
Click Connections, and then select the connection you want.
Click Properties, then check the option for "Refresh data when opening the file."
This ensures that the data is refreshed whenever the workbook is opened, but it still may require edit permissions to refresh externally linked data.

Use Power Query: Power Query is a powerful tool that allows you to import data from another workbook (or other sources) in a more dynamic way. It also provides better control over how and when the data is refreshed. Power Query will allow you to load data from Workbook A into Workbook B and refresh it on demand, but you may need edit permissions to execute the refresh.

Manual Refresh for View-Only Users: Unfortunately, if you're sticking to simple = links and need the data to update when someone with view-only access opens Workbook B, there's no automatic way to refresh it. A user with view-only access would need to have access to refresh manually by either requesting edit permissions or asking someone with editing permissions to refresh it.

I hope you find a solution in one or more of these suggestions. If not, please contact us again.

Kind regards
Marius Barnard
STL