Chart with Table

[Dawn1231]

I am tasked to make a chart each month with the previous 12 months' KPI information. The table has the monthly information for over 3 years.

I am wondering if there is an easier way (I have to do this with >15 charts)

Current solution is that I have a table for data. Each month, I add a row to the table and I hide the months I do not want to show up in my chart.

Then, for every chart, I have to change the data. For this example, I would go in and change every row reference down to row 53 instead of 52 when I add the next month. I tried changing the absolute to something like $R52 instead of $R$52, but it just isn't working for me.

='On-Time Performance'!$Q$3:$R$3,'On-Time Performance'!$Q$29:$R$52,'On-Time Performance'!$T$3:$U$3,'On-Time Performance'!$T$29:$U$52


From here, I refresh all the charts and they're good to go, but I wonder if I am putting in more effort than I need to. Picture below of sample data set, a sample chart, and the data selection

I have never used offset, so I'm not sure if this would work or if anyone has any suggestions. Thank you in advance!

 

[Joe4]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Creating a combo chart with dynamic information
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Kerryx]

Maybe this route
Chart the Last 12 Months Dynamically