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
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
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.
1. Can I use named range in data range box when creating pie chart?
2. Need to adjust existing named range for dynamic charts
3. Use named ranges in array formula
4. Dynamic Named Range In Chart Data Series
5. Named Ranges in dynamic chart issue
6. Using Dynamic Named Range in Chart
7. Using a worksheet's named range in a chart
8. Named range in chart - any way to change the workbook reference with VBA
9. How to use named ranges in excel vba?
10. Named ranges in charts
11. VBA : using multiple named ranges in an address?
12. Using Named Range in VBA
13. Use Named Range in VLOOKUP Formula
14. Named Range In Chart
15. Named Ranges in Array Formulas
16. Named Ranges in External Workbook
17. Using named ranges in forumulas
18. Create dynamic chart using named ranges in VBA
19. Use Dynamic Named Range In Chart
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.
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
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.
I have a file set up which contains 10 named ranges. These ranges refer to different data set in a worksheet within the
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
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?
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
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) _
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
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.
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.
ShLastRow = .Cells(Rows.Count, "M").End(xlUp).Row
Set ShRange = .Range("M7:M" & ShLastRow)
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?
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:
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:
For icolumn = 1 To graphARR.count
.Values = graphARR(icolumn)
.XValues = rngchtxval
.name = rngchtdata(1, icolumn)
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!
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