SOLVED

Using a Named Range in multiple Named Ranges

%3CLINGO-SUB%20id%3D%22lingo-sub-2313941%22%20slang%3D%22en-US%22%3EUsing%20a%20Named%20Range%20in%20multiple%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313941%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20number%20of%20Named%20Ranges%20that%20all%20have%20a%20constant%20%5Bthe%20same%5D%20value.%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%20%3DSheet_Name!%24E%2416%3A%24E%241135%3C%2FP%3E%3CP%3EThe%201135%20is%20the%20value%20in%20question.%3C%2FP%3E%3CP%3EI%20have%20created%20a%20Named%20Range%20Ceiling%3D1135%3C%2FP%3E%3CP%3EI%20am%20uncertain%20how%20to%20revised%20the%20%3DSheet_Name...%201135%20reference%20to%20the%20Ceiling%20Name.%3C%2FP%3E%3CP%3ECan%20I%20do%20that%3F%3C%2FP%3E%3CP%3EHow%3F%3C%2FP%3E%3CP%3EThere%20are%20a%20dozen%20or%20so%201135%20references.%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20globally%20change%20them%20to%20the%20Ceiling%20reference%20vs.%20individual%20edits%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20insights.%3C%2FP%3E%3CP%3EGeo.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGeorge%20Salisbury%3C%2FP%3E%3CP%3ELong%20Valley%2C%20NJ%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2313941%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2313968%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20Named%20Range%20in%20multiple%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1042620%22%20target%3D%22_blank%22%3E%40salsburyg%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMake%20the%20range%20refer%20to%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDIRECT(%22Sheet_Name!E16%3AE%22%26amp%3BCeiling)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20a%20dozen%20names%2C%20it's%20not%20that%20much%20work%20to%20edit%20them%20individually%2C%20but%20you%20could%20also%20use%20a%20macro%3A'%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20Change_Names()%0A%20%20%20%20Dim%20nm%20As%20Name%0A%20%20%20%20Dim%20rf%20As%20String%0A%20%20%20%20For%20Each%20nm%20In%20ActiveWorkbook.Names%0A%20%20%20%20%20%20%20%20rf%20%3D%20nm.RefersTo%0A%20%20%20%20%20%20%20%20If%20InStr(rf%2C%20%22INDIRECT%22)%20%3D%200%20And%20nm.Name%20%26lt%3B%26gt%3B%20%22Ceiling%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20rf%20%3D%20%22%3DINDIRECT(%22%22%22%20%26amp%3B%20Mid(rf%2C%202%2C%20Len(rf)%20-%205)%20%26amp%3B%20%22%22%22%26amp%3BCeiling)%22%0A%20%20%20%20%20%20%20%20%20%20%20%20nm.RefersTo%20%3D%20rf%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20nm%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2313988%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20Named%20Range%20in%20multiple%20Named%20Ranges%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2313988%22%20slang%3D%22en-US%22%3EThank%20you%20for%20the%20%3Dindirect...%20pointer.%3CBR%20%2F%3EI'll%20try%20it%20out%20and%20post%20back.%3CBR%20%2F%3EYes%2C%20there's%20not%20so%20many%20that%20I%20can't%20do%20them%20manually%20-%20I%20was%20just%20being%20lazy%3CG%3E%3CBR%20%2F%3EGeo.%3C%2FG%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies
best response confirmed by salsburyg (New Contributor)
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
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.
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.