Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 9
Default named ranges

Hello gurus,

This is a fun project, or it could be with a little help for you fine folks.

I am establishing named ranges to create dynamic charting. Since I will be
doing 100+ charts for multiple groups at my company, Ive set up a standard
format for each set of named ranges that generate the charts. However, I am
not that far along and defining a new set of named ranges (approx 12 per
chart = 1,200 names) 100 times over has proven quite daunting, even with the
use of the name manager (http://www.jkp-ads.com/officemarketplacenm-en.asp).

Each group will have one workbook, each with one sheet per source data and
another sheet dynamically linking the chart to the source data.

I have adapted the methodology found at Jon Peltiers site
http://peltiertech.com/Excel/Charts/DynamicLast12.html for my charts.

The sheetname format is as follows: each source data sheet name will
increase by 01, so the first source data sheet is metric01, second is
metric02. This way (I hope, when I have one book completed, I can save as
for another group and avoid having to rename all the ranges).

The following is the format for the named ranges in the first tab:
Metric01 named ranges=
M01_chtlen
M01_chtcats
M01_chtvalA
M01_ chtvalA_title
M01_chtvalB
M01_ chtvalB_title
Etc for more data columns (C, D,E)

On the Metric02 sheet, the range names are the same with the prefix changed
to M02_, metric03 prefix M03_ and so on.

The source data is laid out identical for each sheet. My theory was I would
create the same named ranges on every sheet (and then for every workbook for
the different groups) only changing the prefix by one to match the
sheetnames number. Similarly, I would adjust Jons dynamic formulas by
changing all of the sheet references by one to match the new sheets name.

M01_chtlen REFERS TO:
=OFFSET(Metric01!$A$2,COUNTA(Metric01!$A:$A)-1,0,-MIN(M01_chtLen,COUNTA(Metric01!$A:$A)-1),1)

M02_chtlen REFERS TO:
=OFFSET(Metric02!$A$2,COUNTA(Metric02!$A:$A)-1,0,-MIN(M02_chtLen,COUNTA(Metric02!$A:$A)-1),1)

So, the only change in the name and formula is the number "1" to a number "2"

This works, but is taking a crazy amount of time, which like everyone, I
short on. Excel doesnt seem to have a nice way to update the range names
as I would need. But, since it is very systematic updating, I would think
code would work nicely.

Sorry for the long winded message, but your help is greatly needed and
appreciated.

Thanks

 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named ranges [email protected] Excel Discussion (Misc queries) 1 March 21st 06 10:33 PM
Max # of named ranges leaftye - ExcelForums.com Excel Discussion (Misc queries) 1 July 14th 05 07:47 PM
Named Ranges davey Excel Discussion (Misc queries) 5 July 1st 05 05:31 PM
Named Ranges in VBA Bruce Excel Discussion (Misc queries) 1 June 17th 05 03:35 AM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM


All times are GMT +1. The time now is 07:05 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"