Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Named ranges | Excel Discussion (Misc queries) | |||
Max # of named ranges | Excel Discussion (Misc queries) | |||
Named Ranges | Excel Discussion (Misc queries) | |||
Named Ranges in VBA | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |