Chart refers to a Named Range with Offset formula in a closed workbook - MS Excel 2010 1

[westma32]

I am using Named ranges in a workbook (titled Workbook1), which uses the Offset formula to reference a dynamic range. For example,

The Named range is CategoryY, and it refers to the formula =OFFSET('Sheet1'!$A$1,1,0,COUNTA('Sheet1'!$A:$A)-1,1).

In another workbook (titled Workbook2), I have a chart which plots a line graph of the data with a series titled Yvalues, with the SeriesValues ='Workbook1.xlsx'!CategoryY.

When both Workbook2 and Workbook1 are open, the data on the chart plots with no problem. However, if Workbook1 is closed, and I open Workbook2, I get an error message which basically says the chart contains a formula which is not valid. When I look at the workbook links, a pop-up says Excel can't find the name CategoryY, and that it is an "Undefined or non-rectangular name."

I believe I have tracked this problem down to the use of the Offset formula in the Named Range, because if I simply point the range to the column of data, as opposed to using Offset to enable it to be dynamic, Excel doesn't care if I have Workbook1 open or closed. Am I missing something in order to make this work, or is there another way to allow a chart to refer to a dynamic range in a different, closed workbook?

 

[byundt]

AFAIK, you cannot use dynamic named ranges in a closed workbook. Not for charts. Not for worksheet formulas.

What you might consider doing, however, is to build a static formula that refers to the same cells as the dynamic named range. A macro can do this for you.

Sub ChartUpdater()
Dim addr As String, frmla As String, frmlaX As String, frmlaY As String
Dim cht As Chart
Dim ser As Series
Dim i As Long, n As Long
Dim vFrmla As Variant, vX As Variant, vY As Variant
Dim cel As Range, rg As Range, rgY As Range
With Worksheets("Sheet1")
    For i = 1 To .ChartObjects.Count
        Set vFrmla = Nothing
        Set vX = Nothing
        Set cel = .Range("A1")      'This cell used for scratch calculations, then cleared
        Set cht = .ChartObjects(i).Chart
        Set ser = cht.SeriesCollection(1)
        frmla = ser.Formula
        vFrmla = Split(frmla, ",")
        vX = Split(vFrmla(1), "!")
        addr = vX(1)
        Set rg = .Range(addr)    'Set up an equivalent range in active workbook
        n = Application.Min(.Rows.Count, rg.Row + 10 * rg.Rows.Count)
        Set rg = Range(rg.Cells(1, 1), .Cells(n, rg.Column))
        cel.Formula = "=COUNTA(" & vX(0) & "!" & rg.Address & ")"   'Number of cells with X-data in target workbook
        n = cel.Value
        cel.ClearContents
        Set rg = rg.Cells(1, 1).Resize(n, 1)    'Equivalent range in active workbook that matches cells with X-data in target workbook
        For Each ser In cht.SeriesCollection
            frmla = ser.Formula
            Set vFrmla = Nothing
            Set vY = Nothing
            vFrmla = Split(frmla, ",")
            vY = Split(vFrmla(2), "!")
            Set rgY = .Range(vY(1))
            Set rgY = rgY.Cells(1, 1).Resize(n, 1)
            vFrmla(1) = vX(0) & "!" & rg.Address
            vFrmla(2) = vY(0) & "!" & rgY.Address
            frmla = Join(vFrmla, ",")
            ser.Formula = frmla
        Next
    Next
End With
End Sub

Here are links to two sample workbooks that demonstrate the sample macro. You'll need to follow the instructions on the first workbook to get the links set up properly to the second workbook.
[URL unfurl="true"]https://d.docs.live.net/7c3f79142c0adf81/PublicFolder/ChartLinkedToOtherWorkbookx.xlsm[/url]
[URL unfurl="true"]https://d.docs.live.net/7c3f79142c0adf81/PublicFolder/TargetForChart.xlsx[/url]

 

[westma32]

Thanks, I will take a look at this and see what I can do - I was hoping for another way besides VBA, but I will see what I can do. I appreciate the feedback.

 

[Gavona]

Do you need dynamic named ranges? Or could you have a macro in your data workbook that extended the ranges as the data changes? Something like:
Sub Macro1()
Range("myData").CurrentRegion.Name = "myData"
Application.DisplayAlerts = False ' to not display the warning that existing definitions are being changed
Range("myData").CreateNames Top:=True
Application.DisplayAlerts = False
End Sub

Maybe put the code in an event so that it fires when you save the workbook. And/Or maybe a formula in the workbook:
=IF(ROWS(myData)<>COUNTA(A:A),"Please run the macro","RangeNames are ok")
In fact that apptroach will work in the workbook containing the charts as well if you want belt and braces.



Gavin