Solved

named ranges

Posted on 2011-03-25
9
340 Views
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

 
0
Comment
Question by:Excellearner
9 Comments
 
LVL 33

Accepted Solution

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

Expert Comment

by:jppinto
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.

jppinto
Capture.JPG
0
 

Author Comment

by:Excellearner
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
0
 
LVL 6

Assisted Solution

by:FernandoFernandes
FernandoFernandes earned 167 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:
ActiveCell.ListNames
5) Press Alt+F11 again, and see all the names and their ReferTo contents in the column to the right.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 33

Expert Comment

by:jppinto
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.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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
0
 
LVL 6

Expert Comment

by:FernandoFernandes
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
        VBA.DoEvents
    Next

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

Open in new window

0
 
LVL 50

Assisted Solution

by:teylyn
teylyn earned 166 total points
ID: 35220380
Hello,

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


0
 
LVL 6

Expert Comment

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

Thanks, :-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now