Dynamic Named Ranges Viewing in Name Box

D

dakotasteve

I just learning to set up and use dynamic named ranges, unfortunately when I
set them up using the Insert>Name>Define method (giving the range a name in
the Names in Workbook box type), I noticed that the range name I established
doesn't appear in the standard name box immediately to the left of the
formula bar. This makes it difficult to confirm the range is correct. Am I
right about this fact? Is there any way around this so that I can visually
look at my range like I can when I set up a range name the old fashioned way?
Thanks everyone!
DakotaSteve
 
B

Bob Phillips

You are correct. Being dynamic means that it won't necessarily be the same
range at all times, so Excel doesn't store the details of the range.

You could view it by typing

=myRange

in the formula bar, and the range will be highlighted.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bob Umlas, Excel MVP

You can verify it by using F5 (Go To) and typing in the dynamic range name
and ensuring it references the right range. Or, inside the
Insert/Name/Define, when you click on the name, you'll see the range
highlighted by the border when you then click in the Refers-to box.
 
J

JMB

I generally use a vba function to show me the dynamic named range addresses.

Function rngAddress(rng As Range) As String
rngAddress = Replace(rng.Address(True, True, xlA1, True), _
"[" & ThisWorkbook.Name & "]", "", 1, 1, vbTextCompare)
End Function

Copy into a vba module. Then enter
=rngaddress(YourRangeName)
in a cell somewhere on a worksheet.
 
Top