Multiple Named Range questions

[Guest]

Excel 2000

I have a large database which produces 18 charts with 4 measurements for 4+
years - Data, Target, Stretch, Threshold. Instead of changing each of 72
Source Data cells each month to include the new month, I am thinking of
creating named ranges for the 72 columns, and include rows that will take the
data to 2010.

Questions:

1) Will Excel accept 72 named ranges, or is there a maximum?

2) Is there an easier way to enter in the named ranges other than one at a
time? (Insert, Name, Define).

3) How to handle the x-axis to not include the future months/years in the
charts? Will I have to redefine Column A (date) in each of the 18 chart's
Catagegory Axis Label in the Source Data Series box each month?

TIA
CaroleO

 

[Gord Dibben]

Carole

Instead of 72 named ranges..................

See Tushar Mehta's site for a short course on Dynamic Charting and some sample
files.

http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

Alternative site is Stephen Bullen's.

http://www.oaltd.co.uk/Excel/Default.htm

Download either or both of FunChrt1 and FunChrt7


Gord Dibben MS Excel MVP

 

[Pete_UK]

1) I have workbooks with more than 72 named ranges.

2) If you have a header row (which are also the names of the named
ranges), then highlight from the header row to the bottom of the data
and Insert | Name | Create - in the pop-up that appears uncheck Left
Column then click OK. Excel will then ask you in turn if you want to
change the existing definition, so you will need to click Yes 72
times, but it is still quicker than manually setting each range.

3) The answer depends exactly on how your data is set up and how you
use it. I have graphs in workbooks where I paste in different data
each month, adjust the named ranges and then (via formulae) derive
dates from the data in another sheet along with summary information
and this is what feeds the graphs.

Hope this helps.

Pete

 

[Guest]

Hi,

Questions:

1) Will Excel accept 72 named ranges, or is there a maximum?

Answer: Limited by available memory. For more information, check "Excel
specifications and limits" in the On-line Help

2) Is there an easier way to enter in the named ranges other than one at a
time? (Insert, Name, Define).

Answeer: Select a Cell, formular or a Cell range and enter a name in the
Name box instantaneously.

3) How to handle the x-axis to not include the future months/years in the
charts? Will I have to redefine Column A (date) in each of the 18 chart's
Catagegory Axis Label in the Source Data Series box each month?

Answer: Check this URL: http://Peltiertech.com/Excel/Charts.

Challa Prabhu

 

[Guest]

Thanks, Pete - what a neat shortcut. I didn't get the Excel question, but it
worked beautifully!

CaroleO

 

[Guest]

Gord - Thanks for the sources. I'll check them out.

CaroleO

 

[Pete_UK]

Thanks for feeding back, Carole - glad to be of help.

You will get the questions from Excel if you then try to redefine the
ranges (using the same technique) with different data next month (or
whenever).

Pete