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)?
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Hi Glenn - I think that's exactly what I'm looking for, but my charting skills are limited to what Excel does for me (well, maybe a bit beyond that, but not much!).
This is my chart data range currently (even though my data doesn't yet extend through col V - so I have blank data in my chart, which I want to avoid, which also appears is what you're solving above)
=Trends!$B$1,Trends!$D$1:$
My axis label is simply =Trends!$D$1:$V$1 (again, actual data only goes through col R - but we have a big tournament tomorrow)
This is my chart data range currently (even though my data doesn't yet extend through col V - so I have blank data in my chart, which I want to avoid, which also appears is what you're solving above)
=Trends!$B$1,Trends!$D$1:$
My axis label is simply =Trends!$D$1:$V$1 (again, actual data only goes through col R - but we have a big tournament tomorrow)
ASKER
Hi Wayne - hope you're doing well! Reminds me of the good ol' days - haha. :^)
ASKER
Took me a bit to track with what I needed to do, but once I did, it started to make sense. Great solution - thanks much!
If you have the dynamic ranges already for your Totals sheet, you should be able to make use of them in your charts. Can you post a dummy worksheet? Or even an example of one of your chart series ranges?
Wayne