May 01 2021 08:50 AM - edited May 01 2021 09:02 AM
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
May 01 2021 09:04 AM
SolutionMake 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
May 01 2021 09:11 AM
May 02 2021 07:39 AM
May 01 2021 09:04 AM
SolutionMake 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