named ranges

Posted on 2011-03-25
Medium Priority
Last Modified: 2012-05-11
Dear experts,

In a spread sheet i have several named ranges, i need to clairfy the following:

1. Is there a feature in excel where i can look at the various name ranges, and in that i can findout the sheet and cell range the name range refersto

2. Can a macro be provided to me where by it will in a new sheet list all the name ranges in column A and then in column B it will list the sheet name it refers to and in column C it will list the cell range.

If there any other special conditions for the name ranges then these can also be referred to in the columns adjacent tot he name range.

Thank you

Question by:Excellearner
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 33

Accepted Solution

jppinto earned 668 total points
ID: 35219745
Ctrl+G takes you go Goto dialog box where you can see your named ranges.
LVL 33

Expert Comment

ID: 35219799
If you have Excel 2010, you can go to Formula tab, and click on Name Manager. It will open a dialog window like the attached image.


Author Comment

ID: 35219831
Hi JPpinto,

thank you for your comment, i have excel 2007.

Kindly advice how to access this information.

For this limited purpose, i woul dprefer a macro to extract this information for me.

Thank you
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Assisted Solution

FernandoFernandes earned 668 total points
ID: 35219844
You can use Ctrl+F3 to get to that box.

You can also:
1) insert a new sheet
2) press Alt+F11
3) Ctrl+G
4) Type:
5) Press Alt+F11 again, and see all the names and their ReferTo contents in the column to the right.
LVL 33

Expert Comment

ID: 35219846
In 2007 Excel you have also this Name Manager under the Formulas tab. It will display all of the information that you want.

Expert Comment

ID: 35219847
p.s.: After you type the command on the immediate window, do not forget to press <ENTER> at the end of that line

Expert Comment

ID: 35219856
I also like to run the code below BEFORE you run the method ListNames of the Range object.
reason: invisible names do not get listed in the sheet.

Sub MakeNamesVisible()
On Error GoTo ErrorTrap
Dim wbk         As Workbook
Dim nm          As Name
    With Application
        .DisplayAlerts = False
        .CalculateBeforeSave = False
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
    Set wbk = ActiveWorkbook
    For Each nm In wbk.Names
        if not nm.Visible then nm.Visible = True

    With Application
        .DisplayAlerts = True
        .CalculateBeforeSave = True
        .EnableEvents = True
        .Calculation = xlCalculationSemiautomatic
    End With
Exit Sub
Resume Next
End Sub

Open in new window

LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 664 total points
ID: 35220380

you can get a list of range names and what sheets and cells they refer to by using Alt - i - n - p and then click Paste List. Or click the Formulas Ribbon > Use in Formula > Paste names > Paste List

cheers, teylyn


Expert Comment

ID: 35220402
teylyn, amazing!!
I never knew where the ListNames method of the Range object was triggered from Excel's interface !

Thanks, :-)

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question