Dynamic Chart Range - Unable to Enter Named Range in Series

[Seugim]

Hello everyone,

I have created dynamic chart ranges in the past using this tutorial (http://www.exceldashboardtemplates.com/how-to-make-a-dynamic-chart-using-offset-formula/), but am having trouble with my current project.

The Named Range offset formulas seem to be working correctly, when I click the "Refers to" box in the Name Manager, they correct cells are highlighted. I cannot seem to get the named ranges into my graph though.

The file name is test.xlsx
The data is entered into the worksheet "Activity History 2017" (There will be a new data sheet for each year, see below for more on that).
The calculations for the graph ranges are done in the worksheet "Calculations" (This will eventually be hidden).
The graphs I would like to put in a worksheet "Graphs".

CapacitySeries: =OFFSET(INDIRECT("'"&Calculations!$B$8&"'!"&ADDRESS(4,Calculations!$B$10)),0,0,1,Calculations!$B$12)
PrioritySeries: =OFFSET(INDIRECT("'"&Calculations!$B$8&"'!"&ADDRESS(3,Calculations!$B$10)),0,0,1,Calculations!$B$12)
SupportedSeries: =OFFSET(INDIRECT("'"&Calculations!$B$8&"'!"&ADDRESS(36,Calculations!$B$10)),0,0,1,Calculations!$B$12)

My intention is that the end user will enter a starting month, year, and a range (e.g. one month). In the calculations page I use MATCH to find the correct columns for starting/ending range, and then calculate the difference for the offset. The ADDRESS function is because I wanted my graphs to be able to use dynamic worksheet references (e.g. if they entered the year 2018, it would automatically go to "Activity History 2018" worksheet). As I mentioned above, the named ranges seem to work as they select the correct cells when I am in the name manager.

I cannot seem to get them into the graph, though. I create a blank 2D line graph, "Select Data", and "Add Legend Entries". Under "Series Values" I have tried:

• =test.xlsx!CapacitySeries
• ='test.xlsx'!CapacitySeries
• =CapacitySeries
• And varies versions of the above using Worksheet references in the front.

Everyone returns the same "Excel found a problem with one or more formula references in this worksheet" error.

Please help, I am running out of things to break!

Thanks in advance,
-J

 

[JLGWhiz]

the way I read the tutorial is that you only need to enter the name of named range for your series. No equal signs and no workbook/worksheet references. Those should be taken care of in the RefersTo part of the name manager. Check the tutorial again and see if you agree.

 

[Seugim]

Thank you for the reply! I tried all sorts of formatting changes, but with no luck. I finally figured out a solution, and thought I would share for anyone that stumbles on this post in the future.

I was focused on my offset formula, but after looking at it more times than I would like to admit, I was certain it was ok. I looked back at some previous projects I had done with dynamic graph ranges, and found that some of the named ranges were created with the scope of "Workbook", but some were created with the specific worksheet as the scope. All the named ranges in this project were at the "Workbook" level, so I created the exact same named range, but changed the scope to the "Graphs" worksheet...and it worked! I have no idea why the named ranges with a workbook scope didn't work, but I can finally move on.

 

[JLGWhiz]

Thank you for the reply! I tried all sorts of formatting changes, but with no luck. I finally figured out a solution, and thought I would share for anyone that stumbles on this post in the future.

I was focused on my offset formula, but after looking at it more times than I would like to admit, I was certain it was ok. I looked back at some previous projects I had done with dynamic graph ranges, and found that some of the named ranges were created with the scope of "Workbook", but some were created with the specific worksheet as the scope. All the named ranges in this project were at the "Workbook" level, so I created the exact same named range, but changed the scope to the "Graphs" worksheet...and it worked! I have no idea why the named ranges with a workbook scope didn't work, but I can finally move on.

Thanks for the feedback. I also have problems with graphs, even when doing it manually. Fortunately, I am now retired and do not have so many to deal with as I once did. Glad you found a solution.
Regards, JLG