Difference between absolute and relative reference in pasted Links.

[vaxo]

Hello Friends, I have macro which Pastes links to copied Cells with formating, it copies links with absolute reference: Example: =Diff!$C$6 and not relative reference Example: =Diff!C6, I have question what is the difference between to of them in this case in which method is preferable, I am financial auditor and sometimes links are Broken and Reff errors occurs.

[HansV]

Let's say the formula =Diff!$C$6 is in E2.
If you now select E2, copy it and paste (normally) in E4, then E4 will also contain the formula =Diff!$C$6.
But if E2 contained the formula =Diff!C6, then E4 will contain =Diff!C8 after copying and pasting.

It's similar when you fill down (or to the right).

Which of these is preferable depends on your purpose. One is not intrinsically better then the other.

[vaxo]

Thanks it is clear, I have one more question: Sometimes I have this links across Workbooks, and when someone or myself change file name or file location of source/master file formulas are broken and completly lost. Are there anyway to update this source files and link's would be not broken?

[HansV]

In the target workbook, select File > Info > Edit Links to Files

S2492.png

Then click Change source.

S2493.png

(In older versions of Excel it looks slightly different but the options are the same)

[vaxo]

Thnaks very much.