Forum Discussion

jenschjoll's avatar
jenschjoll
Copper Contributor
Jun 25, 2024

building charts

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?

Charting
Excel
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jun 25, 2024

    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?

     

Resources