Relative formula not updating when copying


I know that isn't a satisfying answer but its a reasonable work around.Links to external workbooks are created in a relative manner whenever possible.When you copy a formula from row 1 to row 3, all cell rows in the formula increase by two.To prevent a column or row from changing, prefix it with a dollar sign.You can copy a formula from one worksheet to another using the same process.



Yeah, this behavior would be perfect if I were moving entire directories of files.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.This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook.