Excel – Creating a chart using a dynamic named range in Excel 2010

chartsmicrosoft excelmicrosoft-excel-2010named-ranges

I am trying to create a scatter plot in Excel 2010 using dynamic named ranges and am having trouble getting it to work. Here's simple example that is failing:

Open Excel, starting a new workbook
Enter some data:
In cell D1, enter: $A$1:$B$5. (In my real sheet, this is dynamically computed, but manual entry still has the problem).
On the ribbon, click Formulas, Define Name. Define MyRange1 as a sheet-local name using =INDIRECT(Sheet1!$D$1) as shown below:
Click OK, and then insert a scatter chart.
Open the "Select Data" dialog and enter ='Sheet1'!MyRange1
Excel crashes…

Microsoft Excel has encountered a problem and needs to close.

The problem occurs both on Windows XP and Windows 7 with Excel 2010 in both cases, repeatable every time.

I've also tried:

Defining separate ranges for x and y data and using the Edit Series dialog. After entering ='Sheet1'!MyXRange in the X value field, Excel stops accepting keyboard and mouse input except for the Escape key which exits the dialog. If I go back to the dialog only then does it crash.
Scoping the named range to the workbook instead of the worksheet. This actually does stop the crash, but I get errors in the Select Data dialog depending on whether I type =MyRange1 or ='Sheet1'!MyRange1:

Reference is not valid.
A formula in this workbook...

Is this a known issue, or is there somewhere to report it? I don't have Excel 2007 or 2003 here to check if the problem is isolated to 2010. If I can't get this working I'll probably just use VBA instead of dynamic named ranges.

Update: I thought I figured it out (I posted an answer, now deleted). I changed the value in cell D1 = $A$1:$B$5 to D1 = 'Sheet1'!$A$1:$B$5, and the chart is created properly. However, it seems when the chart was created it is not dynamic — it just used the current values to create the X and Y series, so changing D1 does not make the chart update.

Best Answer

According to the link below and my personal testing of it, you must enter the data series in the form of

'WorkbookName.xls'!RangeNameX

If your name is dynamic, then the resulting chart will be too. The formula I used for my dynamic named range was (modify for your situation and use for both X and Y ranges).

=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)

That worked to make the chart dynamic as far as adding newly appended values to the chart (required both X and Y values before chart updated).

Source for how to Setup Chart - Microsoft Q&A Site Source for hot to use Dynamic Ranges - OZGrid

Related Solutions

Excel – How to create a chart in Excel with dynamic series based on the data content

Here's another approach (assuming Excel 2010):

Convert your data into a Table. Select any cell in your data set and select Insert > Table.
Create a Pivot Table. With your new Table still selected, select Insert > PivotTable.
Setup your Pivot Table. For your example, set your Pivot Table as follows:
- Row Labels: Date
- Column Labels: Series
- Values: Values
Create a Pivot Chart. With your Pivot Table selected, go to Insert > Line with Markers (Chart). The format to your preference.

Chart Example

The major advantage of using this setup is that when you update info in your Data Table and refresh your Pivot Table, your Pivot Chart will automatically be updated with any new dates, series or values.

Excel – Can the name of a named range be dynamic

This assumes that the value in A1 will be entered rather than set by a formula. Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim str As String
    str = Range("A1").Text
    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If n.RefersTo = "=Sheet1!$A$2:$A$4" Then
            n.Delete
        End If
    Next n
    ActiveWorkbook.Names.Add Name:=str, RefersTo:="=Sheet1!$A$2:$A$4"
End Sub

Because it is worksheet code, it is very easy to install and automatic to use:

right-click the tab name near the bottom of the Excel window
select View Code - this brings up a VBE window
paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

bring up the VBE windows as above
clear the code out
close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

EDIT#1:

to use A1 & B1 as the Name, just replace:

str = Range("A1").Text

with:

str = Range("A1").Text & Range("B1").Text

Best Answer

Related Solutions

Excel – How to create a chart in Excel with dynamic series based on the data content

Excel – Can the name of a named range be dynamic

Related Question