Excel 2010 relinking only one cell at a time,rather than alll at once.

[rickj65]

I have an Excel 2010 spreadsheet that uses 3 other spreadsheets as data sources which are used in many (a few hundred) VLookup formulas in the main spreadsheet.

Since the main spreadsheet has been copied down from a shared network directory, when I call the main spreadsheet up on my Windows 7 workstation, I get the expected "This workbook contains links to other data sources" warning message, and I proceed to the Edit Links dialog box where I can change the links to point to the file locations on my local workstation.

However, when I change the source for the spreadsheets, the main spreadsheet only updates the links one cell at a time...re-prompting me for the location of the data source each time. It does this rather than automatically cycling through every cell and updating the whole spreadsheet at once. I have no idea what is causing this since I've been able to relink the data sources fine in Excel 2007...and other users on their Excel 2010 workstations are able to relink without any issues.

Any ideas what might be causing this behavior?

 

[SkipVought]

hi,

Please explain what you are changing the link from/to. This does not sound like a particularly sound practice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rickj65]

I probably didn't explain it very well...it is actually a very common procedure in Excel.

The main spreadsheet that I am using was downloaded from a network drive. Within that main spreadsheet, there are 3 data sources (connections to other Excel spreadsheets) that are used in formulas within the main spreadsheet...for VLookups. When I download the file locally to use, I need to relink those 3 data sources to point to the source files on my local workstation as the old connections are no longer valid.

When proceeding through the relinking process (Data-->Edit Links), normally you highlight the data source (Excel file in my case) and click on the Change Source button which allows you to select a file as the new data source for that link. Normally you select that file once and Excel proceeds to update the link in every formula it is referenced in your spreadsheet. Once that process is done, you are now pointing to the data files on my local workstation rather than pointing to the data files on the network.

The problem I'm having is during the "Change Source" process, rather than clicking on the file once and having Excel update all references in every applicable cell automatically, Excel is only updating one cell at a time...prompting me continuously for the location of the source file I wish to link to. If I have 200 cells that reference an external data source, then what is occurring (improperly) is that I would have to go through the Change Source process 200 times, rather than just once.

One thing of note is that this does not happen with all of my Excel files. There are some Excel files where I edit the links and it updates all of the references at once. It seems like it is this one file that it is forcing me to manually change the source for each occurrence it appears in the main spreadsheet.

Hopefully I've explained the behavior...or misbehavior more clearly here.

Any thoughts?

 

[SkipVought]

do a WORKBOOK FIND/REPLACE using the OLD & NEW link strings.

This will make the change in each formula in the entire workbook with ONE OPERATION.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[rickj65]

Yeah, I tried this, but I get an error message stating "A formula in this worksheet contains one or more invalid references." It doesn't allow me to proceed with the REPLACE. :-(

 

[SkipVought]

THEN you are not doing the replace properly.

This does work IF DONE PROPERLY, which means the resulting syntax is valid.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hoaokapohaku]

Your problem may be caused by invalid named ranges rather than invalid references in formulas. When you use the Edit Links dialog, it will update the references in the formulas, but not in the named ranges.

From the Formulas tab, open the Name Manager and correct or delete any names with reference errors and see if that eliminates the problem.

As you mention, your method is used often, but as Skip says, it's not a particularly sound practice. When it is done, it too often results in the kind of problem you are having, especially if the workbook has named ranges.

One way to overcome this is create your own workbook that stays on your own computer that includes the information needed from the other file. It can even be a complete duplicate of the entire other file. Then, use VBA to write a Workbook Open Event that updates the values in your workbook from the values in the workbook on the network drive each time your file is opened. This way you always have current data, but in a workbook that doesn't have links that get broken.

 

[rickj65]

Hoaokapohaku,

I believe your suspicion regarding named ranges was correct since I recall performing copy & paste processes that overwrote previous named ranges. That certainly could have had detrimental effects on the relinking process. Rather than trying to troubleshoot specifically which named ranges were invalid and then trying to resolve this with brute force, I went back to square one and downloaded all of the original spreadsheet data sources from the network and reran the process of editing links and it worked without any hitches.

Thank you and SkipVought for your efforts to help me get through this issue.

You both mention that the current methodology I described is not a "sound practice". What would you suggest as an alternative? I inherited support of this existing process which equates to "spaghetti code" through multiple layers of spreadsheet cell references and it has been quite challenging to say the least. My goal is to take whatever data and processes currently being used with Excel and migrate it to an Access database to gain more stability and data/version control.

 

[SkipVought]

I'd suggest using MS Query to access data in these other workbooks. The Connection String can be easily and simply changed. The ONLY things that MUST be identical across ALL of them are 1) identically named SHEETS, 2) identically named COLUMN HEADINGS.

How did SOME workbooks/Sheets get range names and some not? This could be simply a quality control issue that could be solved by a simple Event macro.

But my general observation is that it seems a process run amok.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Hoaokapohaku]

Rick,

Glad you got it worked out. As for an alternative, I mentioned one in my earlier post, but I'll endorse Skip's MS Query suggestion. In fact, I can use that idea in some of my own stuff. Thank you Rick for presenting the problem that got us thinking, and thanks for the idea Skip!