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

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)" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> 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/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)" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> 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/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)" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 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
>>
>>
>
>