Free Microsoft Excel 2013 Quick Reference

Using named ranges in dynamic charts (excel 2007)

Excel 2007. Working with a dynamic chart, using named ranges. A couple of
challenges for someone clever...

1. The first formula works as a data series, the second will not. When I
type or paste the second into the forumla bar for the chart, it will not let
me enter out. No error display either. It's like there's a problem with the
formula. However, the named ranges seem to be valid, since I've used them
elsewhere. So what's the difference?

a. =SERIES('Yearly Analysis'!$A$4,'WFG
Finances.xlsx'!TotRevenueYTDlabels,'WFG Finances.xlsx'!TotRevenueYTDvalues,1)

b. =SERIES('Yearly Analysis'!$A$4,'WFG

Both are exactly alike except for the names of the ranges. These are valid
ranges (I can use them for other things) and they are al scoped for the
entire workbook.

2. This second question is even more tricky. And hard to describe. See
formula a. above in question #1. I can create a chart adn type or paste this
formula in the formula bar to create a series on the chart. If I then
left-click on the graphical display of the data series (say a line on a line
chart) then the formula will reappear in the formula bar. HOWEVER...if I save
and close the workbook, then open it back up, I can never get the formula to
display again, by clicking on the data series or by any other method. Can
anyone think of why?? This is important because I need to be able to come
back later and see the formula used. This is just plain weird. And in the
newly opened workbook if I then create another chart and formula, then THAT
new formula will display whenever I click on the data series, but only until
I save and close the workbook. Once I open it up again, then that new formula
will no longer display either.


Thanks for any help.


I have 75 pie charts to create today. Is there a way to use named ranges in
the data range box to speed this up?


I already have set up name ranges for dynamic charting. I use this for it:

=OFFSET('Filtered Data'!$J$2,0,0,COUNTA('Filtered Data'!$J:$J)-1,1)

However now my data is changing and I need to do a count on the first row to start with instead of defining it myself. It could be J19 one time and the next be J23. I never know for sure. For my A column though there will be header info in the first 10 rows or so.

Any ideas on how this can be accomplished?



I am trying to use named ranges in an array formula and it won't work. Below
is the current array formula as entered in cell E8.

{=D8+SUM((Replacements!$C$3:$C$15="NE-777-IUC")*(Replacements!$K$3:$K$15="March")*Replacemen ts!$N$3:$N$15)}

Following are the named ranges from another worksheet withing the same
Name range for Column $C$3:$C15 = RBU
Name range for column $K$3:$K$15=RMO
Name range for Column $N$3:$K$15=RLeas

How would I rewrite the array formula to use only the named ranges? Any
help will be greatly appreciated.


I am attempting to use a dynamic named range in a chart data series as described in Excel Hacks (Hack #42 and Hack #52).

Using Insert|Name|Define I have created a named range called CashFlowSaleChartDataRange that is set to the following value:

=OFFSET('Cash Flow-Sale'!$O$10,0,0,NumFlows,1)

When I subsequently assign a reference to this named range in the data series dialog it will accept the answer and my chart will adjust to reflect the updated range. However, if I return to the data series dialog, I now see the range address returned by the OFFSET function rather than the named range reference.

What am I doing wrong?

I have a file set up which contains 10 named ranges. These ranges refer to different data set in a worksheet within the file.

Using these ranges I need to set up dynamic charts. I have done this for 3 of the charts without issue. However, I cannot create any more.

This are the steps I have been taking:

Copy an existing chart.
Select the trendline so that the formula is visible in the formula bar.
Change one of the named ranges
Press Enter and the new chart is created!!

However, after creating 3 of them it no longer works. I change the name in the formula bar and press enter buit NOTHING happens. it is as if the enter button was not pressed. I do not get an error from Excel.

Any help would be very much appreciated. I can attach the file if it helps.


In one tab, "Irrig Data," I have a data set spanning several years, with one entry per month. I have created a named range of the current year's data with dimensions 1 Column by N Rows, where N is the number of entries made so far this year. In other words, this range expands as I add new entries to the spreadsheet. Here is what I used to name the range:

The first parameter calculates a reference to the first entry of the current year (yes, my data start at the 7th row).  The
next two parameters are simply filler, since I don't need to adjust the range I am referencing.  The last parameter counts
how many entries have been made this year, which sets the number of rows to use for the range.

When I try to use this range in a chart, I get the following error:

A formula in this worksheet contains one or more invalid references. Verify that your formulas contain a valid path, workbook, range name, and cell reference. Any ideas on how I can get this range to show up in my graph by avoiding this error?


I have a chart with a series whose formula looks like this:


The named range, BodOblActData, is a named range in the workbook, but how can I change the formula to use a named range in the worksheet?


Here is my problem. I create an interactive report from a master workbook.
This uses charrts which are updated automatically when new data is read in.
So I am using named ranges on the charts. However, in the source data for
the chart is have to state the workbook name and the range, i.e.

However, when I run the code to hide all data etc from the user I save this
as a different name. Is there a way to change the source range for the
chart? Hope thanks makes and thank for any help.

I have worksheet that I use for LEAN managment with risk management, etc. on the first sheet there is a user form that inputs data to the "database" sheet.
Database sheets has several columns of data and if i want to insert a column at the A1 column then it messes all my comboboxes and textboxes that refer
to the "database" sheet. The problem is I hardcoded all comboboxes and textboxes on the userform and now I have to add some more options on the user form that inputs data on the "database" sheet.

I searched on the internet and found that I could use "named ranges" instead so that I can insert new columns without messing up my comboboxes and textboxes. I tried expermenting with named ranges without sucess so maybe one of you guys can point me in the right direction.

Public Sub Save_Click()

Dim NextRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Databas")

Dim rg As Range
Set rg = Worksheets("Databas").Range("Risk")

' The statement simulates activating the last cell in column A, pressing End,
' pressing Up Arrow, and then moving down one row. If you do that manually,
' the cell pointer will be in the next empty cell in column A — even if the data
' area doesn’t begin in row 1 and contains blank rows.

NextRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

rg.NextRow.Value = Me.cboRisk.Value

' ws.Cells(NextRow, rg).Value = Me.cboRisk.Value

ws.Cells(NextRow, 3).Value = Me.txtProblem.Value

ws.Cells(NextRow, 6).Value = Me.cboStatus.Value

End Sub

I have used named ranges in charts and worked fine. Its been a while and I named some ranges as usually, then when I aselect the chart and try to modify the values by inputting the new name (is range B8:B1000) it wont take it. I know I must just not rememeber how to do it. Got me frustrated.

I tried many things: { and ={" etc.....

how do I type in the name so it works????


This one is probably simple but I can't find the answer in the help menu, my book, or searching the board....

How can I use named ranges in an address in order to copy them?

This works:

This doesn't

I also tried putting the named ranges in quotes like this:

How do I use a Named Range in the code below?
Column "M" is the named range, "ExpiredCerts", note how the sheet range starts in row 7.
I am trying to avoid changing column letters as I add or remove columns.
With Sheets(1)
        ShLastRow = .Cells(Rows.Count, "M").End(xlUp).Row
        Set ShRange = .Range("M7:M" & ShLastRow)
End With


Need a little help with a formula. I have a named range formula and I would like to use that named range in my formula.

Here is the formula for the Named Range:"LastRow"
Here is the formula for the cell, Not sure why it is ref the workbook name? I changed to the worksheet but it changes back to
the workbook.

Thanks for the help, Mike

How do you create a named range in a chart? Therefore allowing it to select only data that is brought back as an actual value rather than #num?

Thanks in advance


Can I use Named Ranges in Array Formulas?

Example here :
{=SUM(IF((ResourceName=$A28)*(ProjectID=$C28),OTLH ours))}

I get the following error:
Value not available error

I have a large amount of data in a workbook. One of the worksheets is a summary of the data, with vlookup formulas (among others). I have used named ranges in the data.

I would like to have the summary sheet in its own workbook so that it's small enough to email, but when I copy the sheet to another workbook the formulas with a named range in them rerurn a #VALUE! This only happens when I click "Yes" to the message that says "The workbook you opened contains automatic links to information in another workbook. Do you want to update...?" Any suggestions on how to get the new workbook to recognize the named ranges from the original one?

Hi, All,

I have a datasheet with columns of demographic data and rows of geographies, and I would like to sum the columns of demographic information using named ranges rather than cell addresses. So, for example, I would like to use a formula similar to this:

=sum(white, black, asian, native_american, pacific_islander)

I have named my columns of data using the header row, so when I use a formula like the one above, I get a total for the full datasheet. But I would like to get the totals by ROW (i.e, by each geography).

I assume I need to name my ranges of data to reflect this, but I'm not sure how. Can someone advise?

Many thanks -- and please forgive me if this has been addressed recently or is (gulp) painfully obvious to a more careful Help reader.


Hey Guys, I am trying to create a dynamic chart in VBA. I have defined a bunch of ranges in the excel workbook which correspond a dynamic range. The user is prompted with a userform, and then based on his decisions I want to make a graph. The userform will turn cells either "True" or "False", and if a call is true, I want to add a corresponding named range which represents a dynamic range. So long story short, I create an array of strings which corresponds to the way excel would handle the dynamic range with charting (i.e. graphARR(1)='==Workbook x'!Range_1 and so on and so on, up to 10 entries in graphARR). Later, when graphARR is full of the strings which represent the dynamic ranges I want, i use:

    With ActiveChart.SeriesCollection.NewSeries 
        .Values = graphARR(icolumn) 
        .XValues = rngchtxval 
        .name = rngchtdata(1, icolumn) 
    End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
However, there is an error when it tries to call graphARR(icolumn). I know there are values in graphARR because when I put in a messagebox higher up in the code, it will return the value (i.e. "==Workbook x'!Range_1"

Does anyone know how to get this to work, or some sort of work around? Thank you very much for all your help!

Hi all!

I am trying to use named dynamic ranges in a chart.

The chart wizard Step 2 has two tabs: "Series" and "Data Range"

The "Series" tab hold named ranges just fine but it accepts ranges that have only Column of data. I need to chart a large number of columns. For that I would use "Data Range" tab. There I would specify the whole range containing by series of data - something like
a1:z100. That would work fine. But I want dynamic range . so I created the dynamic range that refers to columns a-z 10, lowest rows used called it NAME. I enter that NAME into the "data range" box on the "Data range" tab. Wizard accepts the name and looks at my range correctly. HOWEVER, if I go to edit box on the "Data range" in the "data range" box, I don’t see my NAME anymore!! I do see the actual range =a1:z100

Can I have my "data range" box hold a range name?

In Excel 2007 when you name a range for a Lookup table,it does not list the name range. This information is cached somewhere and if you try to use the same name range in another worksheet, it says its already in use.

In previous versions of Excel, you had the option to add/delete the name. Does anyone know how to remove the cached name ranges in Excel 2007?


Hi all,

I'm setting a chart up using dynamic named ranges in the series.
The main series uses a named range called Question001 which I've shown below:
This returns, as an example, the following array:
Though it may return as little as two sets of numbers or as many as twenty-six.
The actual series formula is:
Is there a way that I can add another series which would give me an average line, without having to add more rows of data to
my already massive table of raw data - and more named ranges as everything has to be dynamic (and the chart wizard won't
accept OFFSET formula).
I was thinking something along the lines of:
I'll also be adding extra series based on the values in Question001 - things like MAX(Question001)+4000 which will give me an
area above the chart to place the important value labels (the chart will look messy otherwise).

And then there's another formula to calculate the error bar height so I can have a drop line from my labels to the chart itself.

If I can do all this without having to create extra rows of data then that would be a godsend.

Thanks in advance for any help offered.


I have written an excel macro that makes a copy of the current tab, asks for the user to enter a variable (called "strFile" in this case), then labels the new tab with this variable, and uses the variable in cells.

However I have embedded scrolling charts that use named ranges in the original tab. When the tab gets copied, the charts continue to reference the original tab and not the new one.

Does anyone know the correct VB code so that a variable input string can be used as part of the 'values' and 'x-axis labels' calculation?

The macro I have so far is -
Sub CopySheet()
Dim Response As Integer

' Displays a message box with the yes and no options.
Response = MsgBox(prompt:="Base new summaries on similar 'Unit Operation' resources (ie; Granulator to Granulator, or Coater to Coater). Are you sure you want to create a New Resource Summary Sheet based on this Resource?", Buttons:=vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
strFile = InputBox("Enter The New Resource Name", "Enter The New Resource Name", "")
' The no button was selected.
Exit Sub
End If
If SheetExists((strFile)) = True Then
MsgBox "A sheet with this resource name already exists!"
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveSheet.Copy After:=Sheets("Resource Utilisation")
ActiveSheet.Name = strFile

ActiveCell.FormulaR1C1 = strFile
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Function

and the approach I was going to take was -
Sub add()
' add Macro
' Macro recorded 29/06/2007 by tjs20186

lbls = "=" & strFile & "!LABELS"
ActiveCell.FormulaR1C1 = strFile
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = lbls
ActiveChart.SeriesCollection(2).XValues = lbls
ActiveChart.SeriesCollection(3).XValues = lbls
ActiveChart.SeriesCollection(4).XValues = lbls
ActiveChart.SeriesCollection(5).XValues = lbls
ActiveChart.SeriesCollection(6).XValues = lbls
ActiveChart.SeriesCollection(7).XValues = lbls
ActiveChart.SeriesCollection(8).XValues = lbls
ActiveChart.SeriesCollection(9).XValues = lbls
ActiveChart.SeriesCollection(10).XValues = lbls
ActiveChart.SeriesCollection(11).XValues = lbls
ActiveChart.SeriesCollection(12).XValues = lbls
ActiveChart.SeriesCollection(1).Values = "=" & strFile & "!OFF1"
ActiveChart.SeriesCollection(2).Values = "=" & strFile & "!OFF2"
ActiveChart.SeriesCollection(3).Values = "=" & strFile & "!OFF3"
ActiveChart.SeriesCollection(4).Values = "=" & strFile & "!OFF4"
ActiveChart.SeriesCollection(5).Values = "=" & strFile & "!OFF5"
ActiveChart.SeriesCollection(6).Values = "=" & strFile & "!OFF6"
ActiveChart.SeriesCollection(7).Values = "=" & strFile & "!OFF7"
ActiveChart.SeriesCollection(8).Values = "=" & strFile & "!OFF8"
ActiveChart.SeriesCollection(9).Values = "=" & strFile & "!OFF9"
ActiveChart.SeriesCollection(10).Values = "=" & strFile & "!OFF10"
ActiveChart.SeriesCollection(11).Values = "=" & strFile & "!OFF11"
ActiveChart.SeriesCollection(12).Values = "=" & strFile & "!OFF12"

End Sub

Excel Programming : Using Custom Functions in Dynamic Ranges

Named ranges are great. They enable us to assign a name to a collection of cells and use that name in code and worksheet functions. However, named ranges tend to be static.

Can a named range be used in a chart for one of the series? I have tried
this, and it doesn't seem to work.
For instance, i have a name _Date_rng defined (using Insert...Name...Define)
as "data!$A$2:$A$60". In a chart (XY scatterplot), for one series, i define
the X values as _Date_rng. Excel formats this as ={"_Date_rng"}. However,
the chart is now blank, it draws with the axes, but no data is plotted.
But, when i put the range directly in the chart "Source Data...Series" tab
as "data!$A$2:$A$60", it plots the data correctly.

So, is it possible to use a Defined Name (defined as a range) for the series
of a chart? If not, is there any recommended workaround, as i have many
series i'd like to use the same defined name for, so that i can change it in
one place and all the series will be adjusted.

Thank you . Tom