I suppose that makes sense and now I see when I enter relative
references that the named range changes as I move the selection around
the spreadsheet. The final code that I used (if it will help anyone in
the future) is the following

'PosSpread_Q - Found in the Cross Sectional Quintile data
Range("CA2:DD2").Select
PosSpread = Selection.Find(What:="PtoE SN Q",
After:=ActiveCell).Address(RowAbsolute:=False, ColumnAbsolute:=False)
If Range(PosSpread).Column 26 Then PosSpread1 = Left(PosSpread, 2)
Else PosSpread1 = Left(PosSpread, 1)
PosSpreadRange = "$" & PosSpread1 & "$4:$" & PosSpread1 & "$499"
ActiveWorkbook.Names.Add Name:="PosSpread_Q", RefersTo:="=OFFSET(Data!
$" & PosSpread1 & "$4,0,0,COUNTA(Data!" & PosSpreadRange & "),1)"

Thanks guys for your help,
Brian