Named Range RefersTo input not the same as output-CodePudding

I have a dynamic cascading dropdown (The dropdown values change based on what is selected in the cell next to it) that i have set up on the worksheet "(T) Mechanical".

I want the same data validation list dropdown to work on other sheets, i.e "(T) Lines".

To do this, i'm opting to use VBA to swap out the sheet name in the named range when the worksheet is activated. (The dropdowns use exactly the same cells) -

The below code almost works except that the output of RefersTo is not the same as what gets input, and i cant figure out why its changing between the steps:

Private Sub Worksheet_Activate()

    Dim rng As String
    Dim xWb As Workbook
    Dim xNameString As String 'Existing Name of Named Range
    Dim xName As Name 'Name
    Set xWb = Application.ActiveWorkbook

    xNameString = "Dyn_oSystem"
    rng = "='(T) Lines'!$D2"

    Debug.Print (rng)

    Set xName = xWb.Names.Item(xNameString)

    With xName
        .RefersTo = rng
    End With

    Debug.Print (xName.RefersTo)

End Sub

Output of Debugs

='(T) Lines'!$D2
='(T) Lines'!$D3

What is causing the value shift?

CodePudding user response：

TL;DR: change $D2 to $D$2 in the line rng = "='(T) Lines'!$D2", if you want to create an absolute reference. If you are looking for a relative reference that starts at $D2 (implied R[1]C4 in reference to A1), your method is sound. Just make sure that your active cell is in row 1, if you want to print the reference as $D2 as well.

Unintended effects of relative referencing:

The answer as to why your reference is shifting, can be found

However, if we set the reference for the Named Range to $D2, while our active cell is in row 3, we end up with an unintended result:

Now, the offset will be -1. This causes the reference for row 1 to be pushed all the way to the end of column D (row 1048576).