function trouble

[hans]

I have this function:

Function hoogste(Optional maxweek As String) As String

Dim sh As Worksheet, sName As String
Dim maxindex As Long

maxweek = 0
maxindex = 0
For Each sh In ActiveWorkbook.Sheets
sName = sh.Name
If IsNumeric(sName) Then
If CLng(sName) < 53 Then
If CLng(sName) > maxweek Then
'maxweek = CLng(sName)
maxweek = sName
maxindex = sh.Index
End If
End If
End If
Next
hoogste = "1:" & maxweek
End Function

It returns 1:12 if my highest sheetname is 12.

Now i want to sum cel c10 of all sheets 1 to 12

=SUM('1:12'!C6)
Every time my highest number is an other so i would like to use
=SUM('hoogste()'!C6)

this does not work, what am i doing wrong?

Greetings Hans

PS i use excel 2003

 

[Tom Ogilvy]

The easiest way to do this is to add two additional blank sheets. Name one
Start and one Last
then change your formula to
=Sum(Start:Last!C6)

Place any sheets you want included in the sum between these two sheets (in
the tab order).