Use detailed data in one worksheet to create summary data as chart source

Use detailed data in one worksheet to create summary data as chart source

Post by rdemya » Wed, 24 Jan 2007 23:18:36


I need to export data from MS Access to Excel in order to create a
chart. I can get the data into an Excel worksheet. However, this data
needs to be summarized on a second worksheet. That summarized data
then would be the source for a chart.

My general thinking is to create an Excel template. The template would
have the code necessary so that when a user exports data from Access to
Excel, the following happens:

1) The 'Detail' worksheet in an instance of the template is populated
with the Access data. This would occur via coding in my Access
application. I already have this working.
2) Code in the Excel template instance would then summarize this
'Detail' worksheet data in the 'Summary' worksheet.
3) The 'Summary' worksheet would be already linked to a chart in the
template and, hopefully, would simply update and present the results to
the user.

Users want to be able to change one or more cells in the 'Detail' sheet
and have the changes reflected through to the chart. The reason for
needing to summarize the 'Detail' data is that a direct plot of that
'Detail' data would create a chart that is far too busy and essentially
incomprehensible.

Am I on the right track on how to do this? Unfortunately, I'm not very
familiar with coding in Excel. After some research though it seems to
me that I will need to be able to determine the range of each series on
the 'Detail' worksheet, since I don't know which or how much data the
user has exported from Access to Excel.

Thanks.
 
 
 

1. Rows of data, using status columns to sort data to pie chart on another worksheet tab

2. Chart Source Data Ranges Changing when Data Sheet updated from text file source.

I have a simple Excel wb with 2 sheets. Sheet 1 is simple Line Charts.
Sheet 2 is the data for these charts. The data comes from a text file via
the 'Import External Data' tool.
The text file is filled throughout the day with data every 5 minutes
starting at 00:00. Because the chart will contain 288 data points I've set
my series values to"=<data sheet name>!$C$2:$C$289"

I have 2 questions.
1) Whenever I refresh the Data Sheet from the external text file the series
values in my charts increment by the number of new data points. This is no
good as it causes my charts to appear differently throughout the day. What
could be the cause?

2) Is there a way to chart just cells which have data in them? If I set
the series values in a chart to "=<data sheet name>!$C:$C" it creates a
chart with 65535 data points all but a few blank and the chart is no usable.

Thanks,
--S

3. Create chart using part of data table (data legend)

4. Data Source Configuration Wizard - Problem Creating Data Source

5. Using a column of data from 1 worksheet to extract data from another worksheet

6. refer to data on multiple worksheets using hlookup/look up data on many worksheets?

7. Newbee- How to create chart from data source created in SharePoint Designer?

8. "InfoPath cannot connect to a data source" when designing InfoPath form template using Data Source

9. Can one still create a graph using data in the worksheet?

10. Transform Data task - using 2 data sources in the Source query

11. Linking data from one worksheet to other worksheets using a formul

12. Using EXCEL add-in's worksheet for saving data.

13. Creating a summary sheet from data across multiple worksheets

14. using two set-statement when combining summary and detail data

15. Linking data from one worksheet to other worksheets using a fo