PDA

View Full Version : [SOLVED] Combining Static Series and Dynamic Series in a Dynamic Chart Issues



kewiopex
07-28-2017, 06:15 AM
Dear Experts
I have created a dynamic chart that has a number of dynamic series all plotted against a dynamic horizontal time range.
I wish to end plotting of 1 dynamic series at a specify date and keep this existing graph line intact while maintaining the other series as dynamic series. I have altered the defined name series to a static range and a static range of dates in the "Select data" chart fields. I have also redefined the define name formulas.
However, when the above is altered the other series convert their dynamic horizontal range to the static range .

After reviewing other threads and other sites, there are no sites that explain how to combine a static series while maintaining the other series as dynamic ranges against a dynamic horizontal axis (dates).

Any help would be appreciated.

Also cross-posted on mrexcel.com

mdmackillop
07-30-2017, 07:47 AM
Can you post a workbook with sample layout and data?

kewiopex
07-30-2017, 11:14 AM
Dear mdmackillop
Thank you greatly for considering to look at the dynamic and static series chart issue.

I have attached a sample Test file that contains the chart and the chart table that provides the source data for the chart. Defined Names have been created for X, Y and Z and Date that is used as part of setting up the dynamic series for the chart table data.

0ou will note that in the sample and for this test, I stopped adding data to Y after Q4 2016 but added the data for X and Z series. However, as I mentioned the Y has not adapted and should have stopped at Q4 2016, but did not. I wish to keep Y charted for legacy reasons. I have tried to remove the Defined name formula and Source data, but have been unsuccessful.

I am now suspecting that you cannot combine a static and dynamic series in a dynamic chart configuration.

Any insights would be greatly appreciared.

mdmackillop
07-30-2017, 01:18 PM
Should it be like this?

kewiopex
07-30-2017, 01:37 PM
Yes, you have got it.

The Y series should have stopped 5 horizontal points back from the other series. As I mentioned, I want the X and Z to remain dynamic while the Y series does not continue beyond the last data input but shows correctly at its last data point (date)

mdmackillop
07-30-2017, 02:59 PM
Sub Macro4()
Dim r

With Sheets("ChartData")
Set r = Range("$B$2:$B$5")
Set r = Union(r, r.Offset(, 5).Resize(, Range("Date").Cells.Count))

ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Select
ActiveChart.SetSourceData Source:=Range(r.Address)
End With
End Sub

p45cal
07-30-2017, 03:54 PM
Another way; instead of defining your dynamic named ranges from each row, base them on one dynamic named range and just add an offset from that base range to each row. In the attached, I added a named range (xyzbase) based on the headers in row 2. After that I've added 3 more named ranges x1_, y1_ and z1_, which are offset 1,2 and 3 rows repectively from xyzbase. I've changed the source values of the three lines on your chart to these three named ranges. So now it's the extent of your headers which decides what's plotted.
Have I got this right?

(I've just spotted that you already had a named range based on the headers - Date! Base x, y and z off that instead!)

kewiopex
07-30-2017, 05:18 PM
Dear mdmackillop and p45cal
You guys make it look so easy! Wow! A thank you is not enough but it is all that I have with this forum to show my gratitude!

I was knocking myself out to get something to work and to no avail.
I now have 2 solutions and I never thought that it could be vba..ed. Nor did I realize that by offsetting from the header (Date) for each row of data that I could have that row in control so to speak.

More knowledge in my arsenal!


(http://www.vbaexpress.com/forum/member.php?87-mdmackillop)