Updating charts over time

[NJS1982]

Hi all, bit of a long shot, but...

Does anybody know if I can update a chart automatically as more data is populated, over time? In Excel 2016.

I have a set of data in various pivot tables - these Pivots update when I open the workbook each time using auto-refresh within the Options menu of the Pivots.

This Pivoted data then populates a normal table with various formulas in it, which lift data from the Pivots as and when it is updated.

Because I am looking at data with a time stamp (quarterly), I have pre-populated my normal table up until the end of 2020; so anything between today and the end of 2020 is showing as a zero figure (using IFERROR formula to get this) - this is so I can avoid having to add formulas and rows to my normal table each quarter.

I have charts created, using data in the normal table, which basically have data ranges covering data for dates that have passed.

If I want to update the chart in three months to cover another (yet to happen) quarter, then I will manually change the chart data range to cover this new quarter and the chart then updates.

IS there a way at all to make this happen automatically, so if the yet-to-happen quarter that is currently presenting with zero figures becomes populated with actual figures, it shows on my chart?

I don't want to have a chart with yet-to-happen quarters on it, hence the restricted and specific chart data range at the moment.

I have a lot of different charts you see, hence the question (if it was one, manual updates would be fine).

Thanks!

 

[Toadstool]

I may not be understanding the intricacies of your sheet but the two ways I've done this in the past are:

(1) Have the Horizontal Axis based on an Excel Table. As the Table extends the Axis grows.

(2) Have the Horizontal Axis defined for my 12 month cells but suppress the content to nulls for unused months and in the Select Data Source click on "Hidden and Empty Cells" and select "Show empty cells as Gaps".

	C	D	E	F	G	H	I	J	K	L	M	N	O
Re-homed
Dogs
Cats

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Jan-19[/TD]
[TD="align: center"]Feb-19[/TD]
[TD="align: center"]Mar-19[/TD]
[TD="align: center"]Apr-19[/TD]
[TD="align: center"]May-19[/TD]
[TD="align: center"]Jun-19[/TD]
[TD="align: center"]Jul-19[/TD]
[TD="align: center"]Aug-19[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]75[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]49[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]72[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]79[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]62[/TD]
[TD="align: center"]67[/TD]
[TD="align: center"]52[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(SUM(E2:E3)<1,"",EOMONTH(D1,0)+1)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "][/TH]
[TD="align: left"]---ditto---[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1[/TH]
[TD="align: left"]=IF(SUM(O2:O3)<1,"",EOMONTH(N1,0)+1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


<img src="https://i.ibb.co/gZ24Mqn/Graph-Example.jpg">

 

[NJS1982]

Wow, thank, took me a while but works well.
Only problem is I am looking at quarters, not months.
Any idea what the formula will be for this?
i.e. Jan-19 will be Jan-Mar '19, then the next will not be Feb-19, but Apr-Jun '19 etc.?

 

[Toadstool]

I'm sorry but it seems this suppression of empty cells in charts has stopped working for me.

 

[Dave Paradi]

The request to have a chart change the data being charted seems to be popular right now since this is the third request I have seen in the last two weeks. So I wrote up a method that I think would work and just published it at https://www.thinkoutsidetheslide.co...l-chart-with-a-dynamic-range-that-you-select/. I hope that helps.