How can I view / edit Named Ranges without going through tedious Name Manager window?

[d0rian]

My file has several hundred Named Ranges ("NR") that I'm constantly using in formulas / changing. Whenever I want to edit something, I first need to look up just what cell range a particular NR refers to (so I can either change it, or edit the formula accordingly.)

Currently I have to open the Name Manager, scroll down to find the NM, check the Refers To... value to see what it's referencing, etc. Is there a way to speed that process up? E.g. I'm currently looking at this formula:

=INDEX(newdata,MATCH($CB6,newdata_.key,0),MATCH(AA$1,newdata_header,0))

...but I forget just what cell range each of those 3 NR's refers to. What's the quickest way to check that? Can I hover over something that will show the Refers To... value as a tooltip or something? Or perhaps is there a way to immediately bring a particular NR up in the Name Manager for quick viewing/editing?

 

[Johnny C]

if the formulae are short you could use the evaluate tool Formulas - Evaluate formula and step through each variable, that will tell you

 

[offthelip]

the way I would do it is to list out all the named ranges in an extra worksheet, then use a macro to search the active cell for any Named ranges and display the matches in a message box. The code below will do this for you.
I would also assign the macro to a short cut key

Sub ListNames2()
exist = False
For i = 1 To Worksheets.Count
    If Worksheets(i).Name = "NamedRanges" Then
        Exists = True
    End If
Next i
If Exists = False Then
Worksheets.Add.Name = "NamedRanges"
End If
Worksheets("NamedRanges").Range("A1").ListNames


End Sub
Sub findnm()
With Worksheets("NamedRanges")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 2))
End With


Form = ActiveCell.Formula
msg = ""
For i = 1 To lastrow
If InStr(Form, inarr(i, 1)) Then
 msg = msg & inarr(i, 1) & "/" & inarr(i, 2) & Chr(10)
End If
Next i
MsgBox (msg)




End Sub