Using a Named Range in multiple Named Ranges

[salsburyg]

I have a number of Named Ranges that all have a constant [the same] value.

Example:  =Sheet_Name!$E$16:$E$1135

The 1135 is the value in question.

I have created a Named Range Ceiling=1135

I am uncertain how to revised the =Sheet_Name... 1135 reference to the Ceiling Name.

Can I do that?

How?

There are a dozen or so 1135 references.

Is there a way to globally change them to the Ceiling reference vs. individual edits?

Thank you for your insights.

Geo.

George Salisbury

Long Valley, NJ

[#2 · Accepted solution]

@salsburyg 

Make the range refer to

=INDIRECT("Sheet_Name!E16:E"&Ceiling)

With a dozen names, it's not that much work to edit them individually, but you could also use a macro:'

Sub Change_Names()
    Dim nm As Name
    Dim rf As String
    For Each nm In ActiveWorkbook.Names
        rf = nm.RefersTo
        If InStr(rf, "INDIRECT") = 0 And nm.Name <> "Ceiling" Then
            rf = "=INDIRECT(""" & Mid(rf, 2, Len(rf) - 5) & """&Ceiling)"
            nm.RefersTo = rf
        End If
    Next nm
End Sub

[#3]

Thank you for the =indirect... pointer.
I'll try it out and post back.
Yes, there's not so many that I can't do them manually - I was just being lazy<g>
Geo.

[#4]

I applied the =indirect(... and it did what I wanted - thank you.
I varied my Ceiling value and the actions happened and did not happen as I intended.
I'm way far from an Excel maven.
It's all been brute force and trial and error.
I'm sure I would never have looked into the indirect function.
Geo.