Assuming you want a formula like =Sum(Sheet1!sumPremiums)

where sumPremiums is a sheet level name:

.Cells(3, i).Value = "=Sum('" & sName & "'!sumPremiums)"

--
Regards,
Tom Ogilvy

"Stef" wrote in message
...
I gather information from various, named ranges, in a number of worksheets
in
a specific workbook. I use a "For Each loop" to search in every sheet for
values in defined ranges and then place that value in a summary report.
This
works fine with one single value in that range. However, one of these
ranges,
a list, contains numbers that I want to sum up and place in the report.

This is part of the code
...
Next rn
If rnExists = True Then
With totalSheet
On Error Resume Next
.Cells(1, i).Value = sName
.Cells(2, i).Value = Range(sName & "!Database").Rows.Count
.Cells(3, i).Value = "=Sum(Range(sName & !sumPremiums))"
End With
i = i + 1
Else
...

It is the third .Cells-argument that causes my problem. Would be happy
for
any solution.
--
Stef