create dynamic range for chart

[Guest]

Hello,

I want to export data from Excel, perform some calculations on it, and then link the data to an Excel chart. Although the exported data has a named range with it, the area where I've done calculations doesn't have a dynamic named range. It's the calculated area that I want my chart to reference. How can the chart reference only the dimensions of the calculated area that correspond to the variable dimensions of the exported data?

 

[Arvi Laanemets]

Hi

You create yourself dynamic ranges (Insert.Name.Define) for chart data
series - like any dynamic ranges. Just remember for chart you must have
names defined as SheetName.RangeName. And in chart wizard you must refer to
ranges as SheetName!RangeName

--
Arvi Laanemets
(Don't use my reply address - it's spam-trap)

Hello,

I want to export data from Excel, perform some calculations on it, and

then link the data to an Excel chart. Although the exported data has a
named range with it, the area where I've done calculations doesn't have a
dynamic named range. It's the calculated area that I want my chart to
reference. How can the chart reference only the dimensions of the
calculated area that correspond to the variable dimensions of the exported
data?

 

[Jon Peltier]

Not knowing the particulars, it's hard to give a definitive response. If
you have one dynamic range defined in a worksheet, you can easily define
others of the same size using offset. In the Define Names dialog
(CTRL+F3), enter your new range name in the name box, and in the Refers
To box, enter something like this:

=OFFSET(originalrange,x,y)

where originalrange is, uh, the original range, and x and y are the row
and column offsets. If you don't specify new height and width
dimensions, the new range uses those of the original range.

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