?
Solved

Excel charting - dynamically adjust?

Posted on 2016-08-25
5
Medium Priority
?
58 Views
Last Modified: 2016-08-27
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").  The column header here is the name of that corresponding sheet, allowing it to grab cell C2 from that sheet.

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)?
0
Comment
Question by:sirbounty
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 2000 total points
ID: 41771188
You should be able to create a dynamic range name using OFFSET to define the horizontal axis of your chart so that it will automatically update.  The trick will be to create a much larger data set to include future placeholder values that - while returning no results now (#N/A), will be picked up when new sheets are added.

For example, if your current horizontal axis is defined in a row, say cells A1:L1 (12 columns), you could create a dynamic range called "xaxis" with the formula
=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$A$1:$L$1))

Columns A:AZ gives you 52 columns to check, but the range will only apply to the ones with values in them, even if they are formula-driven.

You would then reference that range name in your chart.
=sheetname!xaxis

Likewise, you'll replace your current data range with a dynamic range name so that only valid categories are shown.  Same method as above with xaxis.  If the values are in say, row 12, the range name would have this formula
=OFFSET(Sheet1!$A$12,0,0,1,COUNTA(Sheet1!$A$1:$L$1))

I've attached an example workbook.  I have twelve monthly data sheets and a "Chart" sheet that uses INDIRECT to pull and transpose data.  Note that only ten months are shown on the "Chart" sheet.  If you manually add "Nov" and/or "Dec" to the colored cells (K1 and L1), the chart will automatically update and re-scale.

Regards,
-Glenn
ee-DynamicChartAxis.xlsx
0
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 41771190
Hi Neal! Feels like back in the good old days seeing your name :)

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
0
 
LVL 67

Author Comment

by:sirbounty
ID: 41771540
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:$V$1,Trends!$B$6,Trends!$D$6:$V$6,Trends!$B$10,Trends!$D$10:$V$10,Trends!$B$13,Trends!$D$13:$V$13,Trends!$B$16,Trends!$D$16:$V$16

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)
0
 
LVL 67

Author Comment

by:sirbounty
ID: 41771542
Hi Wayne - hope you're doing well!  Reminds me of the good ol' days - haha. :^)
0
 
LVL 67

Author Closing Comment

by:sirbounty
ID: 41772865
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!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question