Creating A Chart With Embedded Data?

[(PeteCresswell)]

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?

 

[Jon Peltier]

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 here:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.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

 

[(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.

 

[Peter T]

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]

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