Problem using relative path in Excel

Question

 0

Sign In to Vote

I have the following directory structure in Windows 7:

• test excel
  • accounts
    • 2011
      • accounts 2011.xlsx
    • 2012
      • accounts 2012.xlsx
  • reports
    • 2012
      • reports 2012.xlsx

The accounts 2011.xlsx has cell a1 defined as the name value and contains the number 2011

The accounts 2011.xlsx has cell a1 defined as the name value and contains the number 2012

I open all 3 excel files in Excel 2013. In reports 2012.xlsx I define cell B1 as ='accounts 2011.xlsx'!value and cell b2 as ='accounts 2012.xlsx'!value.  Obviously the value for B1 is 2011 and B2 is 2012. I save reports 2012.xlsx and closes all excel files.

Then I rename the folder "test excel" to "test excel moved".  I navigate to folder "test excel moved\reports\2012" and I open reports 2012.xlsx.  I click enable content button then I get the dialog box that says "We can't update some of the links in your workbook right now". I can click either continue or edit links.

The question I have is how can I have relative path stored in reports 2012.xlsx so that if the parent folder is renamed as described above I will not get this error and the links will work.

Answer 1

Links are, I believe, held as relative when the source file is in the same folder as the destination file or it is in a sub-folder of that folder.

In your case the link would have to be to ......\accounts\2012\accouts 2012.xlsx

I suspect such links are not supported.

If you have a number of workbooks in which links like this need to be changed you could try my free LinkManager utility from www.manville.org.uk/software