Hi,
I will soon be working with a rather complex spreadsheet created by someone else. I have a copy to play with and am trying to figure you how everything works.
I have the feeling that there are more range names in the workbook than will display in the GoTo window. Is there any way to get a list of all the range names in the entire workbook?
Also I have the feeling that the range names somehow autoexpand as each new months worth of data is added. The rangenames are data sources for dozens of graphs.
Recently I downloaded a sample chart called funchrt1 by Stephen Bullen. It uses =OFFSET(Sheet1!$A$5,1,0,COUNT(Sheet1!$A$6:$A$2004),1) as the Chart X values.
In one of my graphs the Chart X value is =YTD_1011_Nov_v01.xls!OnList_METRO
the first part is the name of the workbook and the second part is the rangename (which I could only get to by F5 then typing in the range name as it was not in the list).
Is there a method to find out if the range name uses the above OFFSET type forumla?
or it may have some type of formula as there is a parameters page which has
Period = July 2009 - November 2010
so the range name may be linked to this cell
Thanks for any help in understanding what is happening.
capri
Range Name - list and properties
-
- StarLounger
- Posts: 87
- Joined: 20 Jan 2011, 06:42
Range Name - list and properties
Last edited by capri on 31 Jan 2011, 23:27, edited 1 time in total.
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Range Name - list and properties
When in a worksheet, insert - name - define and you should be able to see the name and see what it refers to...
Steve
Steve
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Range Name - list and properties
I'd download and install Jan Karel Pieterse's free Name Manager add-in (there are separate versions for Excel 97-2003 and Excel 2007-2010). It will let you view and edit all defined names in the workbook in a more user-friendly interface than the built-in dialog.
In =YTD_1011_Nov_v01.xls!OnList_METRO, OnList_METRO is a defined name. Use the Name Manager to view its definition.
In =YTD_1011_Nov_v01.xls!OnList_METRO, OnList_METRO is a defined name. Use the Name Manager to view its definition.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 112
- Joined: 25 Jan 2010, 20:19
Re: Range Name - list and properties
For this "Is there any way to get a list of all the range names in the entire workbook?:
Make sure you are in a clean area of a sheet, otherwise you'll overwrite the cell contents
In 2000/2003:
Go to Inset/ Name >Paste... [Paste List] button
in 2007:
On the Formula tab, in the Defined Names group choose Use in Formula> Paste Names.... [Paste List] button
Make sure you are in a clean area of a sheet, otherwise you'll overwrite the cell contents
In 2000/2003:
Go to Inset/ Name >Paste... [Paste List] button
in 2007:
On the Formula tab, in the Defined Names group choose Use in Formula> Paste Names.... [Paste List] button
-
- StarLounger
- Posts: 87
- Joined: 20 Jan 2011, 06:42
Re: Range Name - list and properties
Thanks for all the suggestions. I did the paste list and the range name I was looking for was there (895 on a list of 1735 range names) and it did use the Offset formula.
I will take Hans suggestion and install the Name Manage as with that many range names I will need some help in managing them.
Think that has been the quickest I've ever had not just one but 3 responses.
capri
I will take Hans suggestion and install the Name Manage as with that many range names I will need some help in managing them.
Think that has been the quickest I've ever had not just one but 3 responses.
capri