So what, exactly, does the external-sheet reference to A20 look like? Like this?
Code:
='C:\path\[external.xlsx]Sheet1'!$A$20
If so, I guess what you want is some code that a) looks at the cell's Formula property, b) finds the row number and c) replaces it with a different number.
In the character string "='C:\path\[external.xlsx]Sheet1'!$A$20", the row (20) starts in character position 37. Once you have that position in a variable—let's call it pr, ie the Position of the Row—you can modify the cell's Formula property like this:
Code:
' Assume that co is the cell object we're pointing at.
vf = co.Formula
co.Formula = Left(vf, pr - 1) & Mid(vf, pr) + 2
But how do we find pr? If you know that all the references will be absolute, and (an important point) always will be, that is, the next time you run this macro as well, then you can use InStrRev:
Code:
pr = InStrRev(vf, "$") + 1
If you don't know that, you can still search backward in a loop:
Code:
For pr = Len(vf) To 1 Step -1
If InStr("0123456789", Mid(vf, pr, 1)) = 0 Then Exit For
Next pr
pr = pr + 1
Me, I'm tempted to go with Regular Expressions. But I'm new to them (shame on me), and would have to look up exactly how to do it in this case. Maybe this is enough to get you started.