In the code below I created a dynamic range, the range name is based on
the contents in cell 2 of a column (in this case text) and the sheet
name. How can I then place a formula into the 1st cell of the column
that totals up the contents of the dynamic range, using the dynmaic
range name.

Sub Named_Range()

Dim s1 As String
Dim s2 As String
Dim sName As String
s1 = ActiveSheet.Name
s2 = "'" & s1 & "'"

sName = Cells(2, ActiveCell.Column).Value
ThisWorkbook.Names.Add Name:=sName & "_" & s1, _
RefersTo:="=OFFSET(" & s2 & "!" & Cells(2, _
ActiveCell.Column).Address & _
",1,0,COUNT(" & s2 & "!" & _
ActiveCell.EntireColumn.Address & ")-1,1)"


Debug.Print sName & "_" & s1, ThisWorkbook.Names( _
sName & "_" & s1).RefersTo

End Sub

Thanks
Burl