Dynamic Named range for another worksheet

Question

I have this dynamic named range:

= OFFSET('Sheet1'!$H$1, 0, 0, COUNTA('Sheet1'!$H$1:$H$100)-COUNTIF('Sheet1'!$H$1,0))

It only works for Sheet1.

How can I modify this formula so it works on all sheets? (There are about 20 different sheets named as "2010", "2011", etc).

Edit:

• All the sheets are structured in the same way
• This dynamic named range will be used to update charts
• Is there a way to use INDIRECT?

Answer 1

You need identically named Named Ranges with worksheet scope, not workbook scope.

I've modified your named range definition to the following.

=Sheet1!$H$1:INDEX(Sheet1!$H:$H, MATCH(1E+99, Sheet1!$H:$H))

This VBA will create worksheet scope named ranges for all worksheets in the loop.

Sub createNames()
    Dim wsn As Long, f As String

    For wsn = 2010 To 2019
        On Error GoTo next_wsn
        With Worksheets(CStr(wsn))
            On Error Resume Next
            .Names("Hnumbers").Delete
            On Error GoTo 0
            f = Replace("=Sheet!R1C8:INDEX(Sheet!C8, MATCH(1E+99, Sheet!C8))", "Sheet", Format(wsn, "'0'"))
            .Names.Add Name:="Hnumbers", RefersToR1C1:=f
        End With
next_wsn:
    Next wsn
End Sub