Relative Linking in Excel Formulas

Ken Puls

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,531
Reaction score
6
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Just wanted to make sure I didn't miss anything in later versions...

I have two files, let's call them A and B. Inside A there are a bunch of links to B. I now want to make a backup copy of these files so that I can work on them some more, but retain my history. To do this I want to copy them into another folder (call it v2011-05-31), but then the links would be retained to the original folder (call it vCurrent), which defeats the purpose.

Currently the only method I'm aware of to accomplish relative linking (to make that true backup) is as follows:
  • Open file A & B in the vCurrent folder (causing the links to lose the file path while Excel is open)
  • Do a File-->SaveAs on File B to folder v2011-05-31
  • Close File B causing the full path to now show in links in File A
  • Do a File-->SaveAs on File A to folder v2011-05-31
At this point I have my links repointed in the files saved to the v2011-05-31 folder, and still have my originals in the vCurrent folder.

I know this works, but it's a pain to do. I'm hoping (but not holding my breath) that I just missed the implementation of relative linking in Excel 2007/2010.

Confirmation either way would be appreciated. :)

PS: Actually, it's not the only way, I guess. I could repoint the links by way of the Data-->Edit Links tab, I suppose, but that's scary if you are linking to a lot of files.
 
This probably won't help, but I have been thinking that linking files using formulas is bad practice. An alternative method might be;

I am looking to structure Excel files to include a separate sheet that has output data in a simple table. Then any other Excel files that need to reference that data can have an input sheet, with an ODBC query that references the first workbook.

That way, data connections can be named appropriately (you can even add a description in Excel 2010) and are relatively easy to edit when files are moved.
 
Hi Ed,

Doesn't help for this instance but...

Personally I prefer never to link files. I re-wrote our entire budget package a few years back from a series of 20 linked spreadsheets into templates that read from/write to a database. (I didn't build the original and bit the bullet after 3 years of fighting with chasing the tail of spaghetti around... we were terrified to make any adjustments to the files!)

The ODBC idea is interesting, but if I were going to go that route I'd probably set this up in a database.

In the case of this specific set of files, I spent over a year building the first one (it's a fairly sizeable model.) I then got given another one and had to tie them together, then had to build a third piece on an unreasonable deadline and bring them all together. I'm not happy about the links, and I've tried to structure them as logically and auditable as I can, but they're still messier than I'd like. Unless I get another 4 months to work on rebuilding them properly they're going to stay the way they are. :(

On a global basis, my main concern for about links (and probably yours too) is breakage. I see that issue coming from two potential areas:
  • The files get moved and their links still point to the old file locations
  • Data gets moved within the file when the link host is closed, and it therefore doesn't get updated.
To me, if Microsoft were to give us the ability to use relative file paths in our links, and the ability to target named ranges in other workbooks, I think I would be comfortable using them. At that point it's pretty much got the same stability as VBA code and worksheet range references.
 
One of my staff pointed out to me today that you can already point to named ranges in other workbooks. (Shows how much I use linking!) So that part is already there.
 
Hi Ken,

Yes, connecting to a database is my preference. I thought I would mention that you can also connect to other Excel sheets using ODBC. Some users prefer this, as they may not be conversant with databases.

On to your problem, could you use something like this code to convert existing formulas that reference external sheets to INDIRECT(ADDRESS formulas?

I like INDIRECT(ADDRESS because it contains the (internal or external) sheet reference as a variable. Makes it easy to change.

The code is a quick off-the-top snippet and only converts a simple formula. You may need to tweak this to apply to your situation.

Code: 
Sub ChangeFormula()
' changes the selected formula from direct to indirect addressing
If Selection.HasFormula Then
  Selection.Formula = "=INDIRECT(ADDRESS(" & FormulaRow(Selection) & _
  ", " & FormulaCol(Selection) & _
  ", 1,1, """ & FormulaSheet(Selection) & """))"
End If
End Sub
 
Function FormulaRow(cell As Range) As String
Dim i As Long
  FormulaRow = FormulaRowCol(cell)
  i = InStr(1, FormulaRow, "C", vbTextCompare)
  FormulaRow = Mid(FormulaRow, 2, i - 2)
End Function
 
Function FormulaCol(cell As Range) As String
Dim i As Long
  FormulaCol = FormulaRowCol(cell)
  i = InStr(1, FormulaCol, "C", vbTextCompare)
  FormulaCol = Mid(FormulaCol, i + 1, Len(FormulaCol) - i)
End Function
 
Function FormulaSheet(cell As Range) As String
Dim i As Long
  FormulaSheet = cell.Formula
  i = InStr(1, cell.Formula, "!", vbTextCompare)
  FormulaSheet = Mid(FormulaSheet, 2, i - 2)
End Function
 
Function FormulaRowCol(cell As Range) As String
Dim i As Long
  FormulaRowCol = cell.FormulaR1C1
  i = InStr(1, cell.FormulaR1C1, "!", vbTextCompare)
  FormulaRowCol = Mid(FormulaRowCol, i + 1, Len(FormulaRowCol) - i)
End Function
 
Hmm... will have to play with this one.

I just tried an initial test wrapping the entire link in an INDIRECT function and it didn't seem to work. I seem to remember that this needs a bit of tweaking though... (Unless I fat-fingered it and just can't see it!)
 
To use the INDIRECT function, you would convert a formula like this;
='C:\Users\eferrero\Documents\[CommissionEval.xls]Sheet1'!$G$36
to
=INDIRECT(ADDRESS(36, 7, 1,1, "'C:\Users\eferrero\Documents\[CommissionEval.xls]Sheet1'"))
where
ADDRESS(row no, col no, 1,1, pathname\filename\sheetname)

One caveat, the first formula works if the source sheet is closed, the second needs the sheet to be opened. That means that we can omit the pathname in practice and just write;
=INDIRECT(ADDRESS(36, 7, 1,1, "'[CommissionEval.xls]Sheet1'"))

The second formula is more portable, since we can omit the pathname, the source file can be moved and the INDIRECT formula will work whenever both workbooks are open.

Obviously, the filename and sheetname can be stored in cells or named ranges.

The code I provided converts the first formula to the second. Hmmm, here's a thought, we could also write code to turn the second formula into the first. Then we would be able to convert all explicit external references to INDIRECT formulas, move the workbooks, update the pathname variable, and convert all INDIRECT formulas back to explicit external references. That would give us the best of both worlds. Sounds like something I'm too lazy to write.
 
Thanks Ed. It sounds like something I don't have time for at the moment, but I'll circle back on this one in a couple of weeks. :)
 
You must log in or register to reply here.
Back
Top