• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

File Linking Error

C

Collock

New Member
I have a file which I keep tracks of metrics on a consolidated based from a few dozen other excel files. However, I am having an issue with a couple of links from only one file. So when ever I open up the consolidated file I get a linking Ref error. However, if I open the file which the link is to the error goes away. However, I have 9 other links in the same file which I do not have the same issue. In addition under the same folder I have 7 other files all with the same links (but different projects) that I also have no issues with.


I have tried to reconnect the links I was having issues with. When I close out of both files and open the consolidated file again, I get the same error. So why am I having issues with only three links and I have another 9 in the same files which I don't have any issues with? The only thing I can think of is name I called the named range has some issues.


Example:5015001 Submittal Log.xlsx'!Total_Elec._Submitted_to_Client


I removed the period and I still get the same issues. All my working and issue links are to named ranges.
 
Collock

Depending on the version of Excel your using you could be going past a file length issue, which may include the Named Range.

I'd try renaming the named ranges to short versions of the same

Also even though you may be using say a M: as a drive name I have seen Excel change that to a serverlong path name

So keep file names as short as possible.


Can you put all the files together on a local C: drive ?

That will let you check if it is the file name lengths as it will remove it from the equation.


Avoid using Chart and Print in named ranges as they can cause funny things to happen, I'm not sure what other words are reserved.
 
Don't link excel files.


Just don't. Want to link one cell? Maybe. But these massive ties between different workbooks is often spreadsheet abuse, a class 1 felony in almost every state north of the mason dixon.


I'm not beefing. If it works for you, cool. But quirky little things like this become pretty common.....
 
dan_l


I absolutely agree with you. However, I really don't have a choice. Until my company collects this data in a structured database format designed for my industry (that is willing the spend the money to buy project management software and a real project accounting system) and not due everything in excel files, often in different formats for the same data, I am stuck with using excel. There might be an easier way to import this data into access but due to the differing data formats it is well beyond my skill to accomplish it in a cost efficient way. Linking save me about 2 or 3 days verse manually updating hundreds pieces of data.


@Hui


I don't think that is the problem because I have links which are longer than the example above and work fine. For example this works, 5724001-ADMINISTRATION LOG-REVISED 02.10.10.xlsx'!Total_Elec._Submitted_to_Client.


I am trying to be constant in named ranges across all files.
 
You must log in or register to reply here.
Back
Top