building charts

Copper Contributor

I'm trying to build a chart from data that will be updated monthly. Is there any way to build it so that the chart will automatically update when I update/add data?

1 Reply

@jenschjoll 

Firstly, to get input data ranges that grow as additional data is appended, one should use Excel Tables.  From there on one seems to be wrestling with an antique codebase that is determined to allow its amnesia full rein when it comes to forgetting defined names or structured references at the slightest excuse.  I did get something working by using defined names to refer to the data but adding zero to force the name to return an array rather than a range.

=SERIES(Sheet1!$D$4,Sheet1!date,Sheet1!amount,1)

Sheet1!date
=Table1[Date] + 0

Sheet1!amount
=Table1[Amount] + 0

It worked but kept on telling me that "Excel found a problem with one or more formula references in this workbook".  The chart did change however.

 

I would be interested to know whether anyone else has less painful experiences creating dynamic charts, maybe with Pivot Tables or slicers?