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 do I find the handle owner from a hang dump using windbg?

windbg deadlock handle

How do I find out which thread is the owner of my Event handle in windbg:

In what situation, if we add sorted numbers as keys to a hash table, we can expect the hash to be ordered?

php python ruby perl hash

Is it true that in PHP, when we insert elements into a new hash table using sorted numbers as the keys, then the resulting hash will also be ordered?