Range Name - list and properties

[capri]

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

[sdckapr]

When in a worksheet, insert - name - define and you should be able to see the name and see what it refers to...

Steve

[HansV]

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.

[mbarron]

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

[capri]

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