Dynamic Range Charts Across Worksheets?

[GerbilGod7]

Hi,

I'm trying to create a graph of a dynamic range of cells. In order t
do this, I defined two named functions (X and Y) to graph. Everythin
works great, except that I need to do this for a lot of different set
of data on different worksheets. The parameters for each worksheet ar
the same, but I'm trying to find a way to get around defining a ne
named function for every single worksheet.

Is there some way to set the named function to refer to the workshee
that the graph is located in, instead of a specific worksheet?

Thanks

 

[Tushar Mehta]

A few choices. None very good.

(1) Good news/bad news...
Good There is indeed a way to create a name that refers to data
on the current sheet. See the last paragraph in http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/named_formulas.html

Bad Unfortunately, XL's charting module won't use such a name.
Sorry.

(2) Use the INDIRECT function to create the a named formula that refers
to the 'active sheet.' However, use of the INDIRECT function in a
named formula used in a chart is somewhat flaky in how one sets it up.
Worse, the chart on each worksheet may not actually reflect the data on
that sheet until one clicks in a cell or forces a recalculation (with
F9 on a Windows machine).

(3) Create a single chart and use the INDIRECT function to let the user
select which worksheet data are graphed.

(4) Write a VBA macro that sets up all the worksheet names as needed.

(5) If the different worksheets represent data for different values of
the same 'field' (weeks, products, etc.), you could put everything in a
single table with an additional field that is the value on which the
worksheets were created. Then, create a PivotChart (or a regular
chart) based on this single table.

You may be best off with either (4) or (5).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[GerbilGod7]

okay, so here is one of my Named functions:

=OFFSET('X'!$J$1,'X'!$S$2,0,'X'!$S$7-'X'!$S$2,1)

if I were to use (2), would I just replace the 'X'!$J$1 with
INDIRECT(!$J$1), and so forth?

As far as (4) goes, that sounds like a good option. Are there any
example macros that I could adapt to my purposes?

Thanks very much for the help!

 

[Jon Peltier]

A simple macro might go like this:

Sub NameSheetRange()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
' adjust range address and range name in formula below
ws.Range("A1:A10").Name = "'" & ws.Name & "'!" & "Range_1"
' repeat for all needed ranges
Next
End Sub

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[GerbilGod7]

okay, I gave this a shot, and excel doesn't like it:

Sub DynamicGraph()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Range(ws.Name & "-EjectaX").Name = "OFFSET('" & ws.Name & "'!$J$1,'
& ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"
ws.Range(ws.Name & "-RampartX").Name = "OFFSET('" & ws.Name
"'!$J$1,'" & ws.Name & "'$S$7,0,'" & ws.Name & "'!$S$6-'" & ws.Name
"'!$S$7,1)"

End Sub

The idea was that it would create two new named dynamic ranges for eac
worksheet, with names like "(worksheet name)-EjectaX". I think I messe
up the naming conventions, but I can't find a definition for th
parameter.

Once I get this macro to work, how do I get it to create graphs in eac
worksheet that refer to the range in each worksheet?

Thanks

 

[Bob Flanagan]

Take a look at Quick Charts at http://www.add-ins.com/quickcharts.htm - it
is designed to do dynamic charts across multiple worksheets.

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[GerbilGod7]

I've looked at that already, and it would be great... unfortunately, a
a poor college undergrad working on NASA grant money, I can't reall
afford it.



Thanks thoug

 

[GerbilGod7]

After some tweaking, I've gotten it to work, sorta... Here's what
use:

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Nam
& "'!$J$1,'" & ws.Name & "'$S$2,0,'" & ws.Name & "'!$S$7-'" & ws.Name
"'!$S$2,1)"

In each worksheet, it creates a named range with this name:

EjectaX ...[bunch of spaces]... (worksheet name)

I'm not sure why it's doing this; I've tried:

"'" & ws.Name & "'!EjectaX" etc, but it doesn't make a difference.

How do I get it to name these ranges properly, and ultimately grap
them

 

[Tushar Mehta]

The best way, as far as I am concerned, to get the correct code is to
turn on the macro recorder, create a named formula, turn off the macro
recorder and replace the hard-coded sheet name with ws.name (with the
associated adjustments of double quotes). As a precaution, I would
also ensure that the sheet name has a space in it. That way the XL-
generated code will have the very important single quotes already in
the appropriate places.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Jon Peltier]

As the man says, turn on the macro recorder. I discovered the critical
typo: Your formula leaves out the = in front of OFFSET. Either of these
work:

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX3", RefersTo:= _
"=OFFSET('" & ws.Name & "'!$J$1,'" & ws.Name & "'!$S$2,0,'" _
& ws.Name & "'!$S$7-'" & ws.Name & "'!$S$2,1)"

ws.Names.Add Name:="'" & ws.Name & "'!EjectaX2", RefersToR1C1:= _
"=OFFSET('" & ws.Name & "'!R1C10,'" & ws.Name & "'!R2C19,0,'" _
& ws.Name & "'!R7C19-'" & ws.Name & "'!R2C19,1)"

I did both A1 and RC notation, because the recorder uses RC. I converted
to A1 to see whether that was the problem, and both worked. Then I
noticed the missing "=".

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jim]

Jon,
Is it possible to refer to a set number of cells that shifts down each
day? For example, I am plotting VHLC in four columns on a worksheet
and on a separate worksheet I am graphing the latest 20 weeks worth of
data. Each day the graph advances one day (but drops the oldest day
off the chart) when the new data is entered. So the chart remains the
same size, but the data moves across from right to left as the days go
by and new data is entered.
Then this is done for the next worksheet and another chart is added
below the first, etc.
I have all the code working for creating the charts and formatting
them correctly, but the dynamic ranges are new to me. So far what
I've researched appears to define the starting cell in the code (...&
ws.Name & "'!$J$1...) and goes down from there. I guess I'm wanting
my range to advance down the data so I can keep my old numbers (but
not chart them after 20 weeks).
Hope this makes sense, because it's sure tough to put into words for
me!
Thanks,
Jim

 

[Jon Peltier]

Jim -

I have an example on my site that shows how to chart the last 12 months
of data. The first and last plotted point in the chart both move through
the data. You can adapt it to show the last 20 weeks.

http://peltiertech.com/Excel/Charts/DynamicLast12.html

On Tushar Mehta's site (http://tushar-mehta.com) there are several
examples of dynamic ranges that can be used for different ways to
represent a changing data set.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Jim]

Thanks Jon, I just went to your site and I think I can adapt it quite easily.
Thank you for your help again, you guys do a wonderful job on this newsgroup!!
Jim