In MS Access, I can create a chart object, feed it some SQL, and wind up with a
chart that somehow has it's data embedded in the object: user doubleclicks, and
sees the grid containing data.


I'd like to do something like this except that the charts would appear in an
Excel Spreadsheet instead of on an MS Access Form.

Is there any hope? Or should I just allocate one of the spreadsheet's tabs to
"Data", maybe make it invisible, and point all the charts to ranges on that
tab?
--
PeteCresswell

You can in fact store a limited amount of data right in the chart, in the
SERIES formula that defines each series. This is troublesome, and is limited
not by number of points, but by number of characters it takes to represent
the data. You can see how to do it he

http://peltiertech.com/Excel/ChartsH...ChartData.html

However, I always advise people to put the data into a worksheet range
somewhere and plot this range. It's easy to see the data and debug the
chart, and there are no arbitrary limits set in the days of limited hardware
capabilities.

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


"(PeteCresswell)" wrote in message
...
In MS Access, I can create a chart object, feed it some SQL, and wind up
with a
chart that somehow has it's data embedded in the object: user
doubleclicks, and
sees the grid containing data.


I'd like to do something like this except that the charts would appear in
an
Excel Spreadsheet instead of on an MS Access Form.

Is there any hope? Or should I just allocate one of the spreadsheet's
tabs to
"Data", maybe make it invisible, and point all the charts to ranges on
that
tab?
--
PeteCresswell

Per Jon Peltier:
However, I always advise people to put the data into a worksheet range
somewhere and plot this range. It's easy to see the data and debug the
chart, and there are no arbitrary limits set in the days of limited hardware
capabilities.

That's what I wound up doing - and it's finally dawned on me that the user can
right-click the graph, select "Source Data", and Excel will take him right to
the range behind the chart.

Thanks for the alternative though.
--
PeteCresswell

Just to add, data can be stored in Named arrays for use in the series
formulas. In XL 97 & XL2k the limit is 5000+ points per series irrespective
of number of characters in each value, I've not tested the limit in later
versions.

If interested (contact below) I have an addin that does -
- Replace all chart data in cells with named arrays or text (titles etc)
- Re-source chart data to a new range, whether or not currently 'de-linked'
- Dump all named chart data to cells, for record or to amend & make a new
chart

A workbook could comprise of just chart sheet(s)

Main purpose is for removing links to other workbooks and/or re-sourcing
data, or maintaining a static record of a chart.

Although it might be possible to adapt, for the OP's described objective it
would be much simpler to dump data to cells and make a chart in the normal
way.

Regards,
Peter T
pmbthornton gmail com

"Jon Peltier" wrote in message
...
You can in fact store a limited amount of data right in the chart, in the
SERIES formula that defines each series. This is troublesome, and is
limited
not by number of points, but by number of characters it takes to represent
the data. You can see how to do it he

http://peltiertech.com/Excel/ChartsH...ChartData.html

However, I always advise people to put the data into a worksheet range
somewhere and plot this range. It's easy to see the data and debug the
chart, and there are no arbitrary limits set in the days of limited
hardware
capabilities.

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


"(PeteCresswell)" wrote in message
...
In MS Access, I can create a chart object, feed it some SQL, and wind up
with a
chart that somehow has it's data embedded in the object: user
doubleclicks, and
sees the grid containing data.


I'd like to do something like this except that the charts would appear
in
an
Excel Spreadsheet instead of on an MS Access Form.

Is there any hope? Or should I just allocate one of the spreadsheet's
tabs to
"Data", maybe make it invisible, and point all the charts to ranges on
that
tab?
--
PeteCresswell

Peter -

I declined to mention the defined name technique, because it's a bit tricky
to accomplish, the data is not viewable without somehow dumping the named
array into a worksheet anyway, and it does not accomplish what people
(usually misguidedly) want, which is to divorce a chart from the data
source. The data source is no longer linked to the cells in a worksheet, but
it is still not part of the chart: it still resides in a workbook. If you
will be linked to a workbook anyway, it may as well be to data you can
easily look at for validation and verification purposes.

Or simply copy a picture of the chart and paste that into the worksheet.
This prevents the user from (easily) altering other details in the chart.

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


"Peter T" <peter_t@discussions wrote in message
...
Just to add, data can be stored in Named arrays for use in the series
formulas. In XL 97 & XL2k the limit is 5000+ points per series
irrespective
of number of characters in each value, I've not tested the limit in later
versions.

If interested (contact below) I have an addin that does -
- Replace all chart data in cells with named arrays or text (titles etc)
- Re-source chart data to a new range, whether or not currently
'de-linked'
- Dump all named chart data to cells, for record or to amend & make a new
chart

A workbook could comprise of just chart sheet(s)

Main purpose is for removing links to other workbooks and/or re-sourcing
data, or maintaining a static record of a chart.

Although it might be possible to adapt, for the OP's described objective
it
would be much simpler to dump data to cells and make a chart in the normal
way.

Regards,
Peter T
pmbthornton gmail com

"Jon Peltier" wrote in message
...
You can in fact store a limited amount of data right in the chart, in the
SERIES formula that defines each series. This is troublesome, and is
limited
not by number of points, but by number of characters it takes to
represent
the data. You can see how to do it he

http://peltiertech.com/Excel/ChartsH...ChartData.html

However, I always advise people to put the data into a worksheet range
somewhere and plot this range. It's easy to see the data and debug the
chart, and there are no arbitrary limits set in the days of limited
hardware
capabilities.

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


"(PeteCresswell)" wrote in message
...
In MS Access, I can create a chart object, feed it some SQL, and wind
up
with a
chart that somehow has it's data embedded in the object: user
doubleclicks, and
sees the grid containing data.


I'd like to do something like this except that the charts would appear
in
an
Excel Spreadsheet instead of on an MS Access Form.

Is there any hope? Or should I just allocate one of the spreadsheet's
tabs to
"Data", maybe make it invisible, and point all the charts to ranges on
that
tab?
--
PeteCresswell