How to Update Values without having to re-select source files

Is it possible to skip the re-selecting of source files when "Updating Values'? I was hoping to have the same effect as the "auto-update" offer of excel upon opening a workbook where one can simply hit "continue" rather than having to manually "edit links" since the source files actually remain the same.

Is this possible? I need this function as it's hard to restart a big excel file (with other opened files).

Please HELP. I have tried my best researching for previous threads on this, but they are often left with no replies... Thank you very much!

I am using Excel 2007 / 2010

If, by chance, there has been a solved case similar to this, please inform me ( of the link to that thread) as I have not been able to find it by myself. Thank you!

ichiruki17

Excuse me if I misunderstand or state the obvious but in 2003 in Tools>Options>Edit there is a checkbox 'Ask to update Automatic links' if that is unchecked then links are updated automatically.

Does that option not exist in higher versions? Or have I misunderstood?

gmk

Hello GMK!

Thank you very much for replying! I'm not very aware of the older versions (I'm using Excel 2010). In 2010, Data --> Edit Links

I chose "automatic" for update. I've also set the Calculations to be in automatic mode. My workbook does get updated upon the opening of a file. My problem is, I would like to update the workbook after it's been opened (when some editing on the source files were forgotten, and were made while the workbook is already open), my workbook does not automatically update (since i'm using vlookup and not simply referencing a cell). When I wish to update the values, I always need to manually re-select the links, but it is inefficient as the source files are the same source files.

Is it possible to do this? Thank you so much GMK! Panda Hug >:D<

ichiruki17

To be clear....

My workbook does get updated upon the opening of a file.
(since i'm using vlookup and not simply referencing a cell)

How does your workbook get updated when 'a file is opened' if there are no links to the file?
Does this mean you are using the worbooks collection so if the number of wbs increase then your vlookup macro is triggered? And as a consequence that means if you make changes to the source wb there is nothing to trigger the macro?

If this is not the scenario there must be references to the source file in order for an update to take place in your workbook when it is opened.

In other words what comes first. Do you get an update when opening your workbook, in which case there must be links. Or do you get an update when the source file is opened and your vlookup macro is then triggered.

If that is not the scenario then please describe the sequence of events.

SORRY GMK! I think I confused you a lot. I'll try to clarify things:

"How does your workbook get updated when 'a file is opened' if there are no links to the file?"
I have a lot of links to about 5 source files.


Does this mean you are using the worbooks collection so if the number of wbs increase then your vlookup macro is triggered? And as a consequence that means if you make changes to the source wb there is nothing to trigger the macro?
This is not part of my problem.


If this is not the scenario there must be references to the source file in order for an update to take place in your workbook when it is opened.
Correct!

In other words what comes first. Do you get an update when opening your workbook, in which case there must be links. Or do you get an update when the source file is opened and your vlookup macro is then triggered.
I do get an update when opening my workbook. My file does not get updated in real time when the source files are opened (It does if it's simply direct CELL referencing. But with Vlookup, it does not update real time) and this is precisely my problem. Since it does not update real time, I have to update the links using the "Update Values" which requires me to re-select the source file (which I'd like to skip) since it's very prone to error and there are a lot of users who wouldn't know where the source files are located...

If there are still confusing parts, please don't hesitate to ask! Thank you very much GMK! >:D<