CODEWITHSUNDEEP
excelrangeformula
Nat Aes
Nat Aes

Reputation: 927

Referencing A Named Range Whose Name Is Contained In A Cell

I have 200+ named ranges contained in a worksheet, and each of the names are contained in a list in column A. Can I reference the named range through the names in column A?

For example, one named range refers to E10:E16 and is named 'BlueCount' (contained in cell A2). If I enter any of the following formulae, I get the correct result:

=SUM(BlueCount)
=SUM(E10:E16)

However if I try =SUM(INDIRECT(A2)), I get a #REF! error. Similarly, if I break up the name in the formula in any other way (for example =SUM("Blue"&"Count")) the same problem occurs.

Is there any way to reference a named range by referring to the name stored as a string in another cell, or can a named range only be referred directly when writing a formula?

Many thanks for the help!

UPDATE: The named range is a dynamic named range and has been created using the following code:

Sub CreateNames()

    Dim WB As Workbook, WS As Worksheet
    Dim FAVLRow As Long, FAVLCol As Long, i As Long
    Dim MyName As String, Start As String

    Const RowNo = 7
    Const Offset = 1
    Const ColNo = 3
    Set WB = ActiveWorkbook
    Set WS = ActiveSheet

    FAVLCol = WS.Cells(RowNo, 5).End(xlToRight).Column
    FAVLRow = WS.Cells(Rows.Count, ColNo).End(xlUp).Row
    Start = Cells(RowNo, ColNo).Address

    WB.Names.Add Name:="FAVLCol", _
             RefersTo:="=COUNTA($" & RowNo & ":$" & RowNo & ")"
    WB.Names.Add Name:="FAVLRow", _
             RefersTo:="=COUNTA($C$9:$C$100000)"
    WB.Names.Add Name:="MyData", RefersTo:= _
              "=" & Start & ":INDEX($1:$65536," & "FAVLRow," & "FAVLCol)"

    For i = ColNo To FAVLCol
        MyName = Replace(Cells(RowNo, i).Value, " ", "_")

        WB.Names.Add Name:=MyName, RefersToR1C1:= _
         "=OFFSET(R" & RowNo & "C" & i & ",2,0,COUNTA(R9C3:R100000C3),1)"
    Next i

End Sub

Upvotes: 0

Views: 2914

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Check your spelling. Open a new blank worksheet and run the following:

Sub BlueCountTest()
    Dim r As Range
    Set r = Range("E10:E16")
    With r
        .Name = "BlueCount"
        .Value = 1
    End With
    Range("A2") = "BlueCount"
    Range("A3").Formula = "=SUM(INDIRECT(A2))"
End Sub

A3 should display 7

Upvotes: 0

Related Questions