0

I have a graph getting data from a simple 2-row date and production amt. associated with that specific date. However, we are constantly adding more and more rows to this and I would like the graph to be self-updating every time we add more rows instead of having to change the formula's data range every single time we update the sheet.

Right now the formula we're using for the graphs is:

=SERIES(,'PRODUCTION SITE A'!$A$4:$A$125,'PRODUCTION SITE A'!$B$4:$B$125,1). Here's an example spreadsheet on Google Sheets to make it more visible what exactly I'm doing.

jacksons123
  • 171
  • 10
  • something along these lines: https://stackoverflow.com/questions/18272728/dynamic-chart-range-using-indirect-that-function-is-not-valid-despite-range-hi/18275292#18275292 – SeanC May 01 '18 at 18:08

1 Answers1

0

You can graph and chart dynamic ranges by putting the data in a Formatted Table. Highlight everything from [Date - Amt Produced] down to the last row of data (not including Total), and click "Format As Table".

Now, when you add lines to the data, the Table will expand - as will the graph! If it does not automatically expand, you can manually expand the table (and thus the graph) by simply clicking inside the Table, then clicking "Resize Table" in the Table Tools>Design tab.

Notably, if there is a blank line directly below the Table, entering data there will cause the Table to automatically expand its size to include the new data. If you have a Total row directly below the Table, you may have to "Insert" a row above the Total row, then enter data. My suggestion would be to move the Total somewhere else (perhaps above the Table?), so that you can simply type in the next blank line below the Table and have the Table adjust its size for you without the Total getting in the way.