Results 1 to 8 of 8

Thread: Relative Linking in Excel Formulas

  1. #1
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14

    Relative Linking in Excel Formulas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  2. #2
    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.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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!)
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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.

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,091
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •