Relative formula not updating when copying
It's a good thing that Excel doesn't facilitate the abhorrent practice of absolute paths. Therefore, for files that are likely to be moved / changed, I prefer copying the information as values and state clearly the location of the source excel workbook... it's just like naming a range except instead of naming the cell reference you enter the constant. Click New Type Constant1 in Name Type Not Really Me in Refers to.
Now anywhere you put =Constant1 on your spreadsheet it will say "Not Really Me"In real programming languages nobody uses absolute filesystem paths to reference other modules/libraries/classes.
Excel automatically prefixes references to cells in a different worksheet with the worksheet name and an exclamation point, for example, "Sheet1! This means that, if you have the formula "=A1 A2 A3" in cell A4 and you copy the formula to cell B4, Excel assumes you want the formula to be "=B1 B2 B3" and changes it for you automatically.
When you copy a formula from column A to column C, all cell columns in the formula increase by two letters.
It should be done this (sic) way and therefore you are forced to do it their way.