Charting multiple off-sheet data sets
 

I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability to
array names (xData(1), xData(2), etc.); is there any other way to achieve the
same result?

Charting multiple off-sheet data sets
 

You have to define a Name in the workbook, using a formula like this OFFSET,
then link the chart series data to this Name. This is the technique used to
create dynamic charts:

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

The file has to be open if the OFFSET formula is live.

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

"hmm" wrote in message
...
I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and
enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability
to
array names (xData(1), xData(2), etc.); is there any other way to achieve
the
same result?

Charting multiple off-sheet data sets
 

Thanks Jon.

Is there a way to do the same thing for multiple data series?

I know I can one at a time maunually assign names to formulas that reference
the the x and y values.

But can I do so in "one fell swoop"? For example, I would write cell ranges
in columns. Cell A1 would contain the text "C1:C100", A2 "F1:F100", etc.
Similarly, column B would contain textual references to y values (B1,
"D1:D100"; B1, "G1:G100"; etc.). Now I want to plot all the data sets
referred to in columns A and B. Can it be done with just one formula
assigned to one name? Further, can the formula be dynamic, allowing me to
specify how many data sets (i.e. - rows in columns A:B) to plot?

Is there any way to do that, or do I have to manually name the text

"Jon Peltier" wrote:

You have to define a Name in the workbook, using a formula like this OFFSET,
then link the chart series data to this Name. This is the technique used to
create dynamic charts:

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

The file has to be open if the OFFSET formula is live.

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

"hmm" wrote in message
...
I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and
enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability
to
array names (xData(1), xData(2), etc.); is there any other way to achieve
the
same result?

Charting multiple off-sheet data sets
 

Hi,

If you are using Excel 2003 or later it might be easier to define the range
in the source workbook as a List. To do this open the source workbook and
select the entire data area including one row of titles. Then press Ctrl L
and hit OK. (The shortcut for Data, List, Create List)

Now activate the target file and start the chart wizard, pick the type of
chart and choose Next. With the Data Range box active select the new
workbook and then highlight the data area. (You may need to collapse the
Source Data dialog box to select the source workbook.) Click Finish.

Now if the source data changes size the chart will respond dynamically.
--
Cheers,
Shane Devenshire


"hmm" wrote:

I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability to
array names (xData(1), xData(2), etc.); is there any other way to achieve the
same result?

Charting multiple off-sheet data sets
 

Thanks Shane.

Unfortunately I cannot try your suggestion because I am only equipped with
Excel 2000.

"ShaneDevenshire" wrote:

Hi,

If you are using Excel 2003 or later it might be easier to define the range
in the source workbook as a List. To do this open the source workbook and
select the entire data area including one row of titles. Then press Ctrl L
and hit OK. (The shortcut for Data, List, Create List)

Now activate the target file and start the chart wizard, pick the type of
chart and choose Next. With the Data Range box active select the new
workbook and then highlight the data area. (You may need to collapse the
Source Data dialog box to select the source workbook.) Click Finish.

Now if the source data changes size the chart will respond dynamically.
--
Cheers,
Shane Devenshire


"hmm" wrote:

I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability to
array names (xData(1), xData(2), etc.); is there any other way to achieve the
same result?

Charting multiple off-sheet data sets
 

Hi,

Too bad about the version. Just a question - why are you using INDIRECT?
You can use the OFFSET function without it and it doesn't seem to add a
benifit here. I also tested your formula and it returns an error when one
trys to enter it, it looks like you've miss typed it, but I'm not sure what
you want it to read.

--
Thanks,
Shane Devenshire


"hmm" wrote:

Thanks Shane.

Unfortunately I cannot try your suggestion because I am only equipped with
Excel 2000.

"ShaneDevenshire" wrote:

Hi,

If you are using Excel 2003 or later it might be easier to define the range
in the source workbook as a List. To do this open the source workbook and
select the entire data area including one row of titles. Then press Ctrl L
and hit OK. (The shortcut for Data, List, Create List)

Now activate the target file and start the chart wizard, pick the type of
chart and choose Next. With the Data Range box active select the new
workbook and then highlight the data area. (You may need to collapse the
Source Data dialog box to select the source workbook.) Click Finish.

Now if the source data changes size the chart will respond dynamically.
--
Cheers,
Shane Devenshire


"hmm" wrote:

I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability to
array names (xData(1), xData(2), etc.); is there any other way to achieve the
same result?

Charting multiple off-sheet data sets
 

To reference addresses in cells, you'd have to use INDIRECT, and that can be
unreliable when used as chart series data ranges. Use OFFSET, assume that
you're offsetting from A1, and include parameters for RowOffset, ColOffset,
NumRows, and NumCols (the other parameters for OFFSET).

You can't do it all at once, you need a name for each set of X values and Y
values (though is the same X values are used multiple times you can
certainly reuse the names). And you can't use a dynamic range to specify the
number of series to plot, only the number of points in each series.

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


"hmm" wrote in message
...
Thanks Jon.

Is there a way to do the same thing for multiple data series?

I know I can one at a time maunually assign names to formulas that
reference
the the x and y values.

But can I do so in "one fell swoop"? For example, I would write cell
ranges
in columns. Cell A1 would contain the text "C1:C100", A2 "F1:F100", etc.
Similarly, column B would contain textual references to y values (B1,
"D1:D100"; B1, "G1:G100"; etc.). Now I want to plot all the data sets
referred to in columns A and B. Can it be done with just one formula
assigned to one name? Further, can the formula be dynamic, allowing me to
specify how many data sets (i.e. - rows in columns A:B) to plot?

Is there any way to do that, or do I have to manually name the text

"Jon Peltier" wrote:

You have to define a Name in the workbook, using a formula like this
OFFSET,
then link the chart series data to this Name. This is the technique used
to
create dynamic charts:

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

The file has to be open if the OFFSET formula is live.

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

"hmm" wrote in message
...
I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect("
'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and
enter
the name in the x-values box (similar course of action for the
y-values).
But for several data sets, how can I do it? I have not seen the
ability
to
array names (xData(1), xData(2), etc.); is there any other way to
achieve
the
same result?

Charting multiple off-sheet data sets
 

I use INDIRECT because my references are based on formulas; OFFSET, and other
functions like it, require explicit references.

About the error in the formula, I'm not sure. Anyway, in another post Ron
Rosenfeld informed (see link below) that results from INDIRECT.EXT cannot be
used in the OFFSET function. (".EXT" allows reference to a closed workbook.)

http://www.microsoft.com/office/comm...f-5eddce0e2016

"ShaneDevenshire" wrote:

Hi,

Too bad about the version. Just a question - why are you using INDIRECT?
You can use the OFFSET function without it and it doesn't seem to add a
benifit here. I also tested your formula and it returns an error when one
trys to enter it, it looks like you've miss typed it, but I'm not sure what
you want it to read.

--
Thanks,
Shane Devenshire


"hmm" wrote:

Thanks Shane.

Unfortunately I cannot try your suggestion because I am only equipped with
Excel 2000.

"ShaneDevenshire" wrote:

Hi,

If you are using Excel 2003 or later it might be easier to define the range
in the source workbook as a List. To do this open the source workbook and
select the entire data area including one row of titles. Then press Ctrl L
and hit OK. (The shortcut for Data, List, Create List)

Now activate the target file and start the chart wizard, pick the type of
chart and choose Next. With the Data Range box active select the new
workbook and then highlight the data area. (You may need to collapse the
Source Data dialog box to select the source workbook.) Click Finish.

Now if the source data changes size the chart will respond dynamically.
--
Cheers,
Shane Devenshire


"hmm" wrote:

I would like to create a graph from data sets that are referenced by a
formula from another workbook. For example, the x-data could be:

=offset(indirect(" 'C:\[MyFile.xls]MyWorksheet'!a1",0,0,100,1))

It would return the first 100 cells from the specified file.

For one data set, I can set a name "xData" equal to this formula, and enter
the name in the x-values box (similar course of action for the y-values).
But for several data sets, how can I do it? I have not seen the ability to
array names (xData(1), xData(2), etc.); is there any other way to achieve the
same result?