|
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 |
named ranges
Your idea was great! Thank you for your help.
I have been stumped all weekend with trying to pass range names dynamically to the referesto: portion of the code. I am trying to do this since many of the formulas reference other named range. For example, value A in the 1st chart is an offset of the M01 category name. M01_chtValA = OFFSET(M01_chtCats,0,1) Your code would beautifully establish a named range on the second tab as follows: M02_chtValA = OFFSET(M01_chtCats,0,1), however, the formula would be referencing the 1st tab, not the 2nd. Ideally, the prefix M01 within the refersto formula would update to M02_chtvalA= OFFSET(M02_chtCats,0,1). I was trying to establish a variable and pass that through to the formula to no avail. Any additional help would be appreciated. Thanks again! "Don Guillett" wrote: See if this idea helps Sub makenames() For i = 1 To Sheets.Count Sheets(i).Select ActiveWorkbook.Names.Add Name:="sh" & i & "a", RefersTo:= _ "=offset($a$1,0,0,counta($A:$a),1)" Next i End Sub -- Don Guillett SalesAid Software "Stout" wrote in message ... 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, I've 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 Peltier's 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 sheetname's number. Similarly, I would adjust Jon's dynamic formulas by changing all of the sheet references by one to match the new sheet's 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 doesn't 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 |
| All times are GMT +1. The time now is 03:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com