EXCEL 2010 Build a chart based on a named range that may move

I'm struggling to find a solution to something I thought would have been simple. In a separate app, I'm copying an Excel workbook, running a bunch of database queries and dumping the result into the newly copied workbook, giving each result a defined name (named range).

In the Excel 'template' (it's not a real template, just a workbook being copied) there are, on another worksheet, some charts that reference the named ranges.

When I originally built the 'template' I simply inserted a chart and typed the named range in as the data source from the 'Select Data' option. The chart looked fine...

The problem is that the app that dumps the data into Excel won't always put the named ranges in the same location and the chart appears to be looking for a specific range of cells referenced (e.g. =CategoryData!$A$23:$B$29 rather than simply =nrCategoryTotals which is the named range I originally typed. ).

So the question is... Is there anyway I can get the chart to always point to the named range, even if the named range is created in a different location with the following conditions: 1. No user interaction (I realise manually moving a named range does not have this problem as Excel handles the updates) 2. The location and size of the named range will vary, only the sheet it appears on is consistent

I hope that made sense?

Al.

Answers


You need to specify a named range for X values and Y values. You could define two dummy named ranges that point to the first column and second column of your named range.


For example:

X values "Named range" nrCategoryTotalsX: =OFFSET(nrCategoryTotals, 0, 0, ,1)

Y values "Named range" nrCategoryTotalsY: =OFFSET(nrCategoryTotals, 0, 1, ,1)

Then you just need to define your graph with the formula:

=SERIES(,CategoryData!nrCategoryTotalsX,CategoryData!nrCategoryTotalsY,1)

Bear in mind that in this example named ranges are created within scope of CategoryData worksheet.


Need Your Help

How to have image set of jquery event?

jquery html css frontend

So I am trying to get a picture to set off a jquery Show function. I am failing, I have tried a lot of things to no avail. The jquery documentation wasn't helpful for me :(

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.