named ranges

[Guest]

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

 

[Don Guillett]

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

 

[Guest]

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!