Function Is Not Valid On Dynamic Named Range Chart
Apr 20, 2007
I have seen one of your posts in which you make a graph update based on dynamic named ranges. Here is my problem, I already defined the name of my ranges as:
I'm setting something that allows the user to compare bond yield curves on a graph. The setup involves a dropdown to select the final year of data the user wants to see and several date drop-downs.
For example, if the user wants to compare the current yield curve out to 2031 against how the same curve looked the end of Q1 2011 and Q2 2011, he would select "2031" in the first dropdown and then select "4/1/2011", "7/1/2011", and "9/8/2011" in the other three dropdowns.
The data is on a separate sheet, and mechanics of my named ranges are sound. However, Excel won't allow me to add my named ranges to my graph.
I'm trying to replace $B$3:$B$25 with CRV_DATES and $C$3:$C$25 with CRV_ONE (when I get this to work I'll add the other curve to the chart). When I try to add them, though, I can't get out of the series dialogue. When I hit enter, nothing happens--no error message, no nothing. The change simply won't "stick".
Here are my primary named ranges and the ranges/functions I use within them:
I have created a chart that provides a hisotry of events on a timeline. It relies on named ranges and allows me to change the upper and lower date ranges displayed on the x-axis of the chart by changing the date in cells that define the upper and lower dates. In order to display the events on the timeline I have a table that includes 3 columns... a date colmn, an event description column, and a event height column. To display the information I have used a line chart with data points that uses the "event height" data to determine how high up on the graph to display the event information. I have hidden the line, added a label for each event, and added an error bar that draws a line between each label and the bottom of the chart. By default the labels displayed the "event height" information (as expected), so I selected each label individually and added a formula that causes it to display information from the "event description" column instead of the default value. All of this works perfectly with no issues.
The problem comes when I change the date range displayed in the chart. Currently the chart displays event information for 1/1/2012 - 5/30/2012. I can modify 5/30/2012 to be any date I would like that is greater than 1/1/2012 with no problem. Everything displays correctly. However, if I modify the 1/1/2012 date the labels change to reflect the original value (event height data) instead of what I modified it to (event description).
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.
I've read through every thread I can find about dynamic named ranges as source data for a chart. It seems as if everyone can get it to work but me. I have a chart on one sheet that needs to update dynamically from data on another sheet when a button is clicked. Here's my code that runs when I click the button:
Sub Update_Center_Chart() Sheets("Center Data Chart").Select ActiveSheet.ChartObjects("Center Data").Activate ActiveChart.SetSourceData Source = Range("CenterData") End Sub
I get the error message: "Type mismatch." The data is in B1:F2 on a different sheet. The formula in the named range, "CenterData" is this:=OFFSET('Center Data'!$B$1,0,0,COUNT('Center Data'!$B:$B),4). The data will expand by 1 row every time.
Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.
In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter
=--(AND($AX8>$AA$4,$Y8>0))
and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.
Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.
Here's the first hard part:
For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.
Here's the 2nd hard part:
Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.
The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.
Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.
I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.
I try to add a named range and it says name not valid!! I'm putting in jan13. Why would this not be valid. There are no other named ranges in the workbook
I have this formula =COUNTIF(WallA,D35) which works great unless the named range is deleted. Is there a way to check to see if the named range is valid in formula?
I am trying to create a pivot table on a new sheet names as 4x4
I am getting following error:
Error : 1004 Reference Not Valid
I am not sure where is the problem. I have generated this code by using macro recorder and just changed the source and destination of the pivot generation code.
I'm now trying to get my chart to use that range. I read at [URL] .... that I need to make sure my series reference is a fully qualified reference. So I've entered that series reference using the name of the workbook followed by the range name (=2012-PIRS.xlsx!SAM_CLAR2DEL). I have two copies of my workbook (one as xlsm with VBA project, and one with xlsx without VBA) and I can't get Excel to accept the series value in either workbook.
ERROR MSG: The formula you typed contains an error. Try one of the following: - Make sure you've included all parentheses and required arguments. - To use a function, click Insert Function on the Formulas tab (in the Function Library group). - If you include a reference to another sheet or workbook, verify that the reference is correct. - If you are not trying to enter a formula, avoid.........
I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
I need to create a dynamic named range called "DIVLOC," that corresponds to a sheet called Divloc_List in the workbook. Then, I need to take this range and perform a vlookup against it. I tried to create the range in the sheet itself (it wasn't dynamic), and then used that in the vlookup in the vba code, but I got error 2402 (I think). I'm not sure how to define the dynamic range in vba code, and then use the range in the vlookup.
This is happening to several named ranges I created. I’ve tried to correct this by editing the formula which seems to work but changes again. I’ve also deleted the named range and recreated it with the same change process taking place.
In case it matters the named ranges are being created on a table imported into Excel via MS Query.
I am trying to create a dynamic named range to select a list of text entries from a number of different columns that I am using for Data Validation. The columns have entries of variable length, none more than 30. What I am seeking to achieve is that having selected a heading in cell C3 the available list in DV dropdowns elsewhere are governed by the contents of C3.
I have created a defined name, StartPos that uses the contents of C3 to evaluate to the cell reference (e,g $H$4) of the topmost cell in the relevant column. However when I try to use it in the usual formula
=OFFSET($A$1,0,0,COUNTA($A:$A),1) formula I try
=OFFSET(StartPos,0,0,COUNTA(StartPos:[ ],1)
I get stuck at what to put in the brackets. Ideally I would like to use the COUNTA to go down to the last entry in the column. However, even when I try to give the range a height of 30 rows: =OFFSET(StartPos,0,0,30,1) I still get an error.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range. B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I want to create a charts on a pivot table, but I don't like the way Pivot charts function and the limitations they have. So I want to create a normal charts that is dynamic and based on a pivot table.
In order to do so, I want to create dynamic named ranged for the data in the pivot table to use for the data series for the chart.
I can reference the week numbers (headers in the pivot table) for one data series and get the data by this formula:
=OFFSET(Weekly!$C$15,0,0,1,COUNTA(Weekly!$14:$14)-1) $C$15 is the first cell that holds the data for the series. Row 14 holds the week numbers (headers in the pviot)
However, I want to make that formula even more dynamic by having it reflect the changes done to the pivot table who's cells it references to.
So how do I do this using the GETPIVOTDATA function? (or some other function that makes it dynamic and reflect changes done to the pivot table and accounts for the row numbers with the data can change) If I generate the GETPIVOTDATA formula and try to substistute the fixed cell reference for $c$15 with it, the OFFSET formula for the dynamic range gives an error.
And how can I grab the column headers (week number) dynamically?
I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
I have a named range in a column for January data (non-contiguous cells). Every month I insert new data in the column to the right. Is there a way to have a dynamic range that grabs the same exact cells but in the new column but unselects the previous month data? Spreadsheet attached.
In the attached workbook, I have identical sets of data in column A separated by an blank cell. I need a macro for the user form that i need to build. The code needs to find the end of each named range, (Range1,Range2 and Range3), to insert blank row at the end of each range and to copy the value of the textbox from the user form in each row so to keep the sets of data identical.
My file has a code that refers to a dynamic named range. When I run the macro that range gets corrupted and I cannot figure it out why. before the macro the named range refers to:
I have dynamic named range that lists by columns. I can use this with data validation pull down on my worksheet and all is fine. But when I use this named range in the RowSource of a combo box in a userform, I only get the first column of data, or basically, the data in B1, but not C1, D1, ect. Here is the formula for the named range.
I have a sheet containing three drop down boxes which gives me a count of data in a table matching the criteria. (Between 2 dates and equals a depot name).
I am trying to create a chart to only show this data, but I don't want to do it using a pivot table as the end user has no understanding of them and just wants to fill in drop down boxes.
I am beginning to think that using a Dynamic Named Range might be the answer. I believe I am able to achieve what I want with this process but I am not entirely sure how to do it.
In the following line of code in my subroutine I name this range which runs from A2:J2, the problem is later in the routine I delete certain unwanted columns which then shrinks this range. I always want it to be 10 colums wide. How do I make it dynamic with vba? ActiveWorkbook.Names.Add Name:="TranslateShow", RefersTo:=Worksheets("TemplateTest").Range("$A$2:$J$2")
Can I also substitute the workbook actual name for ActiveWorkbook?
When I make a dynamic named range, I typically do something like:
=offset($A$1,,,counta(A:A),counta(1:1))
I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
Anyone run across this.....Over the past 1-2 years...I have setup a number of complex excel workbooks, that automate a lot of reporting for my employees. Suddenly, over the past couple months, I am finding that if I try to go back and edit many of my named ranges (created a while ago) that were created dynamically: "=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A$4:$A$65000),4)"...excel crashes. I can't figure out why.
All I have to do is simply CLICK in the "Refers to:" field of the "Define Name" window (for the dynamically named range)....and I get "Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience."
If I drill down on "What is in the report", I see this "Error signature":..
