Bulk link refreshing in Excel

[JNRH78]

We have thousands of excel files that all contain a link to single 'base data' excel spreadsheet. We update the base data spreadsheet once a quarter and currently we then have to go and open all the the other files one by one and click 'update values' then save and close.

Is there any way to automate this or maybe to select all the files and do a bulk 'update values'?

[#2]

@JNRH78 

I'm assuming (since no detailed information is available) that the workbook links are just simple links.

Excel --> File --> Options --> Trust center --> Settings for the Trust Center ... --> Security settings for workbook links --> Activate automatic update of all workbook links (not recommended) --> OK

Tip: As long as the source is closed, however, the status is displayed as "Error: Source not found". When the source is open as "Source is open". Since the values ​​are updated when the source file is open, I assume that the source file can be reached.

With your permission, if I can recommend you, please always add specific information about your Excel version and operating system to your concerns.

Information on where the file is, such as OneDrive or SharePoint, is of advantage.

As well as, if possible or necessary (not necessary in this case), a file (without sensitive data).

In this way, you will come up with a proposed solution much faster and more precisely.

This also makes it much easier for the helpers to provide the help that they would like to offer you.

It helps both :).

Here is a link with information on how you can quickly find a solution that is most suitable for you.

Welcome to your Excel discussion space!

Thank you for your understanding and patience

NikolinoDE

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

Hi

Apologies, I'm new to this. 

I'm using Excel from the Office 365 suite. I'm using the desktop app. Operating system is Windows 10 Enterprise. 64 bit

So I'm not sure whether the suggestion you made will work, i haven't tried it yet as fearful of the implications to other files. I thought I'd give you some more context first to see if your advice still stands.

So the files I'm talking about are customer price lists. We change our price lists every quarter based on changes in currency and commodity prices. Here is a screen shot of the relevant part of the price list:

The cells A4 and A6 (in red) are the ones that get updated on a quarterly basis. Once updated all teh parts on the price list automatically recalculate to the new price.

You can see the formula in cell A4 is just simply looking up a number from another spreadsheet 'Base Data.xlsx'.

All the files are held in sharepoint.

Currently when we come to create the new price lists for the quarter, we input the new numbers into the Base Data spreadsheet and save it. Then we have to go into each of the individual price list files, open it up, update values and then save. With a thousand price list files, this takes an age. 

Let me know if you need any further information.

James