asked on
Excel charting - dynamically adjust?
I have a sports season stats spreadsheet.
Basically, I have a Totals sheet which through the magic of the indirect function totals all the data on the corresponding sheets that are included between the sheet labeled "First" and the sheet labeled "Last".
Each game, I simply add a sheet prior to "Last" and it's auto updating my Totals sheet.
This is working very well.
I also have a Trends sheet (outside of the First/Last range) that lists individual data/trends for each athlete.
So, for our last game, I might have a stat totaled using =INDIRECT("'"&O$1&"'!"&"C2
Okay, so all of this works fine, until I want to update my charts. I have trendline for all athletes and for the team as a whole.
Each week that we have a game, I have to go into all 13 charts and update the data reference to include the new columns, which point to the new sheets. Is there a way that the charts can be updated to dynamically include charting source as new columns are added, much like what I'm doing with the totals sheets (but for charts)?
Basically, I have a Totals sheet which through the magic of the indirect function totals all the data on the corresponding sheets that are included between the sheet labeled "First" and the sheet labeled "Last".
Each game, I simply add a sheet prior to "Last" and it's auto updating my Totals sheet.
This is working very well.
I also have a Trends sheet (outside of the First/Last range) that lists individual data/trends for each athlete.
So, for our last game, I might have a stat totaled using =INDIRECT("'"&O$1&"'!"&"C2
Okay, so all of this works fine, until I want to update my charts. I have trendline for all athletes and for the team as a whole.
Each week that we have a game, I have to go into all 13 charts and update the data reference to include the new columns, which point to the new sheets. Is there a way that the charts can be updated to dynamically include charting source as new columns are added, much like what I'm doing with the totals sheets (but for charts)?
Microsoft Excel
Last Comment
sirbounty