Excel 2010 - Dynamic Charts - Issue with OFFSET Function

[ttaber01]

Hello,

I'm working to create dynamic charting in my dashboard using the OFFSET Function as follows:


=OFFSET('7'!$G$1,COUNTA('7'!$G$2:$G$25)-1,0,-MIN(12,COUNTA('7'!$G$2:$G$25)-1),1)

As you can see, I want the last twelve months of data in therange.


For the associated dates (to get the last twelve months that havedata values listed) I am using the following OFFSET function.



=OFFSET('7'!$F$2,COUNTA('7'!$F$2:$F$37)-1,0,-MIN(12,COUNTA('7'!$F$2:$F$37)-1),1)



I'm having issues with themonths and the values matching. Can you help me sync these up?

I'm getting 2 month difference in the data being pulled.

 

[Derek Brown]

Try using Dynamic Names.
This may help you with the solution:
https://peltiertech.com/Excel/Charts/DynamicLast12.html

 

[Jon Peltier]

Create a named range for the dates:

Name: MyDates
RefersTo:
=OFFSET('7'!$F$2,COUNTA('7'!$F$2:$F$37)-1,0,-MIN(12,COUNTA('7'!$F$2:$F$37)-1),1)

Then use this as the basis for your named range for the values:

Name: MyValues
RefersTo:
=OFFSET(MyDate,0,1)

By definition it is the same size and uses the same rows as the dates.

 

[ttaber01]

Thank you! I'll give this a try!!

 

[ttaber01]

Jon, It worked great except that when I refer my chart to the named range of "MyValues", it adds a new month to the chart (without a date, e.g. it's blank) so the chart is showing the last 12 months but also shows a blank month that has not been filled in yet. Do you know why?

 

[ttaber01]

Jon,

Another question. I have a large table that will be used to update metrics for various charts in a dashboard. There is multiple months of data for each chart, e.g. may go out to end of 2020 for each chart.

The issue I'm seeing is that I want to name each chart's data separately with a "named region" to then dynamically display the corresponding chart. e.g. If I'm from Sales and I need to enter in a # for sales for the month of September 2018, then they enter that # in the blank field for September 2018 for that chart and it auto displays on the related chart.

Further down in the dataset, there are other teams entering in their data for their charts. The bottom line is that I need to break up the data into multiple regions (1/1/2018 - 12/1/2020) so each chart for that particular team/area updates in the dashboard

Does that make sense? I can share a file with you if that would help

 

[Jon Peltier]

Jon, It worked great except that when I refer my chart to the named range of "MyValues", it adds a new month to the chart (without a date, e.g. it's blank) so the chart is showing the last 12 months but also shows a blank month that has not been filled in yet. Do you know why?

Off by one? Make sure you're counting values properly.

 

[Jon Peltier]

Jon,

Another question. I have a large table that will be used to update metrics for various charts in a dashboard. There is multiple months of data for each chart, e.g. may go out to end of 2020 for each chart.

The issue I'm seeing is that I want to name each chart's data separately with a "named region" to then dynamically display the corresponding chart. e.g. If I'm from Sales and I need to enter in a # for sales for the month of September 2018, then they enter that # in the blank field for September 2018 for that chart and it auto displays on the related chart.

Further down in the dataset, there are other teams entering in their data for their charts. The bottom line is that I need to break up the data into multiple regions (1/1/2018 - 12/1/2020) so each chart for that particular team/area updates in the dashboard

Does that make sense? I can share a file with you if that would help

Maybe you need to count each group's Y values, and base the X values of each group on its Y values. The charts are independent, but based on the same data range.

Name: SalesY
Refers To:
=OFFSET('7'!$QQ$2,COUNTA('7'!$QQ$2:$QQ$37)-1,0,-MIN(12,COUNTA('7'!$QQ$2:$QQ$37)-1),1)
where QQ represents the column containing Sales' Y data

Name: SalesX
RefersTo
=Offset(SalesY,0,-columns)
where columns is the number of columns from QQ to the dates.

Repeat for each group, with its own QQ and columns.