Dynamic charts in Excel 1

[Andrzejek]

I have a graph on Sheet1 and the data this graph is using is on Sheet2. ‘Regular’, simple data: let’s say row 1 has months, column A has peoples’ names, starting from cell B2 there are numbers going down and to the right. Basic data.

I would like to set it up the way so if the data on Sheet2 changes, the graph on Sheet1 displays the current data from Sheet2.

What I mean by: “the data on Sheet2 changes” is, I may have Months in row 1 as Jan, Feb, Mar and I change it to Jan, Feb, Mar, Apr, May . And if peoples’ names change from Andy, Tom, Susie, to Barb, Gary – the graph will display the changes without modifying the settings of the graph.

[pre]
Jan Feb Mar
Andy 12 23 34
Tom 76 65 54
Susie 34 45 32

Jan Feb Mar Apr May
Barb 12 23 34 43 32
Gary 76 65 54 45 34
[/pre]
I did try to set my data as a Table in Excel (click in the data, Insert – Table - OK), but then – how do you replace the data with the new data without wiping out the table? And how do you set the graph to read the data from the Table, and not from Sheet2.Range(“A14”)?

I’ve seen some YouTube videos about dynamic charts in Excel, but they only show how to do it when the data ‘grows’ down. No info about the changes to the data I desire.

I will be populating the data from the data base, and I know how to do it in code.



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Hi,

What type of chart?

What’s on category & value axes?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

It is actually a Column Type of the chart
It has Categories like:
[ul]
[li]No Potential to Cause Effect[/li]
[li]No Historic Properties Affected -- Minor[/li]
[li]No Adverse Effect -- Minor[/li]
[li]No Historic Properties Affected[/li]
[/ul]
Values - just simple counts, numbers from 1 to 50 or so

(If that's what you've asked for...?)


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

value axis has numeric values as per numbers for Names/Months
category axis has either Names or Months.

Don't understand

It has Categories like:
No Potential to Cause Effect
No Historic Properties Affected -- Minor
No Adverse Effect -- Minor
No Historic Properties Affected

Where is THAT in your posted sample data?

Column Type - Stacked or Cluster?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

However, if I understand correctly, just refresh your query and the chart changes accordingly.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Sorry about Categories confusion, I was not sure what you are asking for.

Column Type - Stacked

"just refresh your query and the chart changes accordingly"
as long as I have the same number of columns / rows in my data

If the numbers of columns and / or rows change, my chart displays the new data but encompassing only the original columns/rows setting - ignoring any new columns/rows data.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

I will be populating the data from the data base

I assumed, maybe wrongly, that you have a QueryTable on sheet2 that returns data from a remote database.

Since that QueryTable is a ListObject object, you can create a Chart from that table as source, that will cause the Chart to be “dynamic” as you envision.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Unfortunately the data on Sheet2 is not that easy to get. There is a lot of calculations and manipulation of data. So I actually do that in VB6 code in another application, start Excel and provide the required data to Excel to be displayed in a chart. That's how "I will be populating the data from the data base".

This is just a small part of a huge report done based on many, many tables (no PKs, no relations, really weird business rules, etc.)



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

 

[Andrzejek]

Got it - thank you.
So on sheet Chart, I have a table TableQuery and a chart.
I can change the values in the data and chart shows it OK.

How can I now replace this data with the completely new data with different info in row 1, different info in column A, and with either more columns / rows or fewer columns and rows of data?

Friday, going home.
Be back on Monday...


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Then bring the data into sheet3 as a normalized table.
On sheet2, query sheet3 as a transform, for instance...

sheet3: Data
[pre]
Name Date Value
ben 1/1/2018 12
Andy 2/1/2018 23
Andy 3/1/2018 34
Tom 1/1/2018 76
Tom 2/1/2018 65
Tom 3/1/2018 54
Susie 1/1/2018 34
Susie 2/1/2018 45
Susie 3/1/2018 32
fred 4/1/2018 22
[/pre]

The transform SQL
[pre]
transform Sum(a.Value)
SELECT a.Name
FROM `Data$` a
Group By a.name
pivot a.Date
[/pre]

Resulting in: Sheet2 data
[pre]
Name 1/1/18 2/1/18 3/1/18 4/1/18
Andy 23 34
Susie 34 45 32
Tom 76 65 54
ben 12
fred 22
[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

My example...

The key is to have a QUERY as the data source for the chart. THAT makes the chart react dynamically to changes in the source data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Thank you Skip.
I know your way is (probably) the way to go, the proper way. But I will (probably) go a little different way.

Based on your sample, there is a data in Sheet1 - which I can easily get from my data base. I can run this code (assuming selected sheet from start is sheet Query):

Sub ReplaceDataForChart()
    Cells.Select
    Selection.ClearContents [blue]
    Sheets("Sheet1").Select
    Range("A1:D4").Select
    Selection.Copy
    Sheets("Chart").Select
    Range("A1").Select
    ActiveSheet.Paste[/blue]
    ActiveSheet.ListObjects.Add(xlSrcRange, Range([green]"$A$1:$D$4"[/green]), , xlYes).Name = _
        "Table_Query_from_Excel_Files"
    Range("Table_Query_from_Excel_Files[#All]").Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SetSourceData Source:=Range("Table_Query_from_Excel_Files[#All]")
End Sub

I know I can shorten this code even more (eliminate some .Select, etc.). I can eliminate [blue]blue[/blue] code by dumping the data from DB (instead of copy/paste), and I will know the [green]range[/green] of my data since I will know how many columns and rows of data I get from my data base.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

I really wouldn’t name your table "Table_Query_from_Excel_Files[#All]". It’s misleading since it is a COPY of what seems to be a query table.

However, you got that name from somewhere, perhaps the table on sheet1.

If that’s the case, why bother all the copy ‘n’ paste code? Just run your chart off the sheet1 data. When that query table Refreshes, so does your chart.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

No, I will not call my table Table_Query_from_Excel_Files
And yes, copy ‘n’ paste blue code would be replaced with 'get data from DB' code.

Thanks for pointing me in the direction of (hopefully) working version of my code.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

BTW, The ONLY code I have in my sample is code to keep the query connection PATH in synch with wherever the user saves the workbook.

No other code is required. So I don't understand why you're running code when you state "copy ‘n’ paste blue code would be replaced with 'get data from DB' code".

From what I understand, you run some code to grab data and paste it into your workbook. My suggestion was to make that result a normalized table, then query/pivot to generate the chart source data. That query result looks to me like Table_Query_from_Excel_Files and THAT data ought to be the source data for your chart. No other code is required.

Now I'd strongly suggest using the PATH correction on an appropriate event. And you could do the query refresh in a click event.

But maybe I'm missing something.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

“you run some code to grab data and paste it into your workbook“
Yes.

“make that result a normalized table”
If you suggest to make the data look like the one on the Data worksheet in your example, then yes – I can do that. 3 columns (Name, Date, Value) and several rows of data below

“then query/pivot to generate the chart source data”
Is that the data on Chart worksheet in your example? If so, how is this data connected to Data worksheet? I don’t know anything about query/pivot in Excel, but I can learn.

I did try to add a record in Data worksheet to see if that record will show up in Chart worksheet data, and on the graph, but that did not happen. I must be missing a step or two. :-(


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

”then query/pivot to generate the chart source data”
Is that the data on Chart worksheet in your example?

YES!

If so, how is this data connected to Data worksheet? I don’t know anything about query/pivot in Excel, but I can learn.

FAQ68-5829

From Excel you can query 1) a db like Oracle, SQL Server, DB2, Access or 2) a quasi db like Excel where a sheet is a table or a table named range or even query 3) a text file, provided a Schema.ini file has been set up to define your text files.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]