I want to create a spreadsheet to analyze the membership of a professional society, and one of the analysis tools needs to be a Pareto chart showing how many chapter members work at different area companies. This spreadsheet will be updated with new membership data every month, and the number of companies will change each month. Therefore, I want a dynamic input for the Pareto chart.
I've read several webpages that describe using named ranges and offset commands to create a dynamic input, and I've created a named range (with the appropriate offset formula to determine the number of rows of data) for each variable I want to graph (Pareto_Companies_Horizontal, Pareto_Members, Pareto_Percent), selected the existing data and created the chart. So far, so good.
The problem comes when I try to edit the range for the chart's variables to replace the qualified range with a named range. When I do this and click on 'OK' to accept the changes, I get the error:
Untitled.png
which confuses me because I've used named ranges a LOT in this spreadsheet and I don't see any problems with these three named ranges' definition. To avoid typos, I copied the name of each named range into the definition of the chart's variable's range, but I still get the same error.
Does anyone have an idea of what I'm doing wrong and how to fix it?
The Excel file is attached. When you open it, you will see the Pareto chart whose variables I am trying to program as named ranges. The named ranges are on the tab labeled "Instructions and Scratch Pad".
Bookmarks