Dynamic Range Charts Across Worksheets?

Hi,

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

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

Thanks!


---
Message posted from http://www.ExcelForum.com/

A few choices. None very good.

(1) Good news/bad news...
Good news: 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 news: 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

In article , GerbilGod7
says...
Hi,

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

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

Thanks!


---
Message posted from http://www.ExcelForum.com/

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!


---
Message posted from http://www.ExcelForum.com/

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 wrote:
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!


---
Message posted from http://www.ExcelForum.com/

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 each
worksheet, with names like "(worksheet name)-EjectaX". I think I messed
up the naming conventions, but I can't find a definition for the
parameter.

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

Thanks!


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
Hi,

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

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

Thanks!


---
Message posted from http://www.ExcelForum.com/

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



Thanks though


---
Message posted from http://www.ExcelForum.com/

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

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Name
& "'!$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 graph
them?


---
Message posted from http://www.ExcelForum.com/

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

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

ws.Names.Add Name:=ws.Name & "!EjectaX", RefersTo:="OFFSET('" & ws.Name
& "'!$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 graph
them?


---
Message posted from http://www.ExcelForum.com/

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/
_______

Tushar Mehta wrote:
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.